sql select query in same table

select, sql

Solution

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

Problem

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 a`here). 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.

Original source