How can I selet rows which have 2 columns values cross equal?

This question was initially asked on the postgres.sql mailing list: http://article.gmane.org/gmane.comp.db.postgresql.sql/17005

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.
Fay

Solution was given by Bruno Wolf III:

Self join the table.
Something like (untested):

SQL:

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;

Dialect: Postgresql
Solution by PFC:

What are your conditions on a and b ? Can a be equal to b on a row ? If
so, do you want this row ?
If you want to avoid duplicates, I suggest first removing them, then
adding a constraint CHECK( a &#60 b ) for instance. Then, from you r
application (or in an ON INSERT trigger), swap a and b if a>b.

I added some values to your table for completeness :

SELECT * FROM test;
a | b | id
-----+-----+----
100 | 101 | 1
101 | 100 | 2
100 | 3 | 3
20 | 30 | 4
11 | 13 | 5
3 | 33 | 6
30 | 20 | 7
666 | 666 | 8
666 | 666 | 9
500 | 666 | 10
666 | 500 | 11
123 | 123 | 12
456 | 789 | 13
456 | 789 | 14

Try :

SQL:

SELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS  yb 

FROM test x, test y 

WHERE x.a=y.b AND x.b=y.a;

xid | yid | xa | xb | ya | yb
-----+-----+-----+-----+-----+-----
1 | 2 | 100 | 101 | 101 | 100
2 | 1 | 101 | 100 | 100 | 101
4 | 7 | 20 | 30 | 30 | 20
7 | 4 | 30 | 20 | 20 | 30
8 | 9 | 666 | 666 | 666 | 666
8 | 8 | 666 | 666 | 666 | 666
9 | 9 | 666 | 666 | 666 | 666
9 | 8 | 666 | 666 | 666 | 666
10 | 11 | 500 | 666 | 666 | 500
11 | 10 | 666 | 500 | 500 | 666
12 | 12 | 123 | 123 | 123 | 123

You'll get 2 rows for each match. You can add a condition to remove the
dupes :

SQL:

SELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS yb 

FROM test x, test y 

WHERE x.a=y.b AND x.b=y.a AND x.id[=y.id;

xid | yid | xa | xb | ya | yb
-----+-----+-----+-----+-----+-----
1 | 2 | 100 | 101 | 101 | 100
4 | 7 | 20 | 30 | 30 | 20
8 | 9 | 666 | 666 | 666 | 666
8 | 8 | 666 | 666 | 666 | 666
9 | 9 | 666 | 666 | 666 | 666
10 | 11 | 500 | 666 | 666 | 500
12 | 12 | 123 | 123 | 123 | 123

If you don't want the rows with a=b, replace x.id<=y.id with x.id<y.id

SQL:

SELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS  yb 

FROM test x, test y 

WHERE x.a=y.b AND x.b=y.a AND x.id<y.id;

xid | yid | xa | xb | ya | yb
-----+-----+-----+-----+-----+-----
1 | 2 | 100 | 101 | 101 | 100
4 | 7 | 20 | 30 | 30 | 20
8 | 9 | 666 | 666 | 666 | 666
10 | 11 | 500 | 666 | 666 | 500

It is going to be slow, though. Basically a full self join. Let's hack
this :

SQL:

CREATE AGGREGATE array_accum (

     sfunc = array_append,

     basetype = anyelement,

     stype = anyarray,

     initcond = '{}'

);

I'll leave this one as an exercice to the reader ))

SQL:

SELECT array_accum(id), CASE WHEN a<b THEN a ELSE b END AS low, CASE WHEN  a<b THEN b ELSE a END AS high 

FROM test GROUP BY low, high 

HAVING  

sum(sign(a-b)) = 0 AND count(*)]1;

array_accum | low | high
-------------+-----+------
{10,11} | 500 | 666
{4,7} | 20 | 30
{1,2} | 100 | 101
{8,9} | 666 | 666