sql select query in same table

Question

i have a newby sql related question.

let's say i have this simple table:

A      B
------ ------
a      b
a      c
b      a
m      n
m      o
n      m

i'd like to query for records which have "counterparts" only, i.e. i want to get a b only if there is b a in the table but i want to skip the "back links" (which is b ahere). to sum up i'd like to get the following result

A       B
------  ------
a       b
m       n

this sql query does not work since in the case b a is handled the a b is removed from my result set.

SELECT DISTINCT x1.A, x1.B
FROM TEST x1, TEST x2
WHERE x1.A = x2.B AND x1.B = x2.A -- all records /w counterparts only
AND x1.A NOT IN (SELECT B from TEST where B = x1.A) -- skip the "back links"

the 2nd part of the WHERE clause does not work as desired.

do you have any hints? any help with this would be greatly appreciated.

regards peter

p.s. i am using the derby db.

Answer 1

You could change your last line to:

AND x1.A < x1.B

This assumes that either your columns are never self-referential (eg: a, a) or that you don't want circular references to appear. If you do, then:

AND x1.A <= x1.B

EDIT:

You're probably better off using explicit joins as well:

SELECT DISTINCT 
    x1.A
    , x1.B
FROM 
    TEST x1
JOIN 
    TEST x2
    ON x1.A = x2.B 
        AND x1.B = x2.A -- all records /w counterparts only
WHERE x1.A < x1.B --Skip backreference