Re: How can I selet rows which have 2 columns values cross equal? - Mailing list pgsql-sql

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
;