Date: Fri, 10 Mar 2006 11:31:13 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Fay Du <fay.du@versaterm.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How can I selet rows which have 2 columns values cross equal?
Message-ID: <20060310173113.GA8935@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Fay Du <fay.du@versaterm.com>, pgsql-sql@postgresql.org
References: <001d01c64445$fec23350$b8d849c6@versaterm.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <001d01c64445$fec23350$b8d849c6@versaterm.com>
User-Agent: Mutt/1.4.2.1i

On Fri, Mar 10, 2006 at 08:24:44 -0500,
  Fay Du <fay.du@versaterm.com> wrote:
> Hi All:
> I have a table like this:
> 
>    Table test
> Id   |  a | b
> -----+----+---
> 1    | 100| 101
> 2    | 101| 100
> 3    | 100| 3
> 4    | 20 | 30
> 5    | 11 | 13
> 6    | 3  | 33
> 7    | 30 | 20
> 
> I want to get row 1, 2,4 and 7 selected. Because their values of a and b
> are cross equal. i.e., for each pair of rows, 
> a.Row1 = b.Row2 and b.Ro1 = a.Row2
> a.Row4 = b.Row7 and b.Ro4 = a.Row7
> 
> How can I construct a sql statement to select them?
> Thanks in advance.

Self join the table.
Something like (untested):

SELECT t1.id, t1.a, t1.b
  FROM test t1, test t2
  WHERE
    t1.a = t2.b
    AND
    t1.b = t2.a
  ORDER BY t1.id
;