Hi 2011/5/15 Seb <splu...@gmail.com>: > Hi, > > This probably reflects my confusion with how self joins work. > > Suppose we have this table: > > =# SELECT * FROM tmp; > a | b > ---+--- > 1 | 2 > 2 | 3 > 4 | 5 > (3 rows) > > If I want to get a table with records where none of the values in column > b are found in column a, I thought this should do it: > > =# SELECT * FROM tmp t1, tmp t2 WHERE t2.b <> t1.a; > a | b | a | b > ---+---+---+--- > 1 | 2 | 1 | 2 > 1 | 2 | 2 | 3 > 1 | 2 | 4 | 5 > 2 | 3 | 2 | 3 > 2 | 3 | 4 | 5 > 4 | 5 | 1 | 2 > 4 | 5 | 2 | 3 > 4 | 5 | 4 | 5 > (8 rows) > > I need to get: > > a | b | a | b > ---+---+---+--- > 1 | 2 | 1 | 2 > 4 | 5 | 4 | 5 > > Or just: > > a | b > ---+--- > 1 | 2 > 4 | 5
Your query doesn't have an explicit join and is producing a cartesian result. I don't think a self- join will work here; a subquery should produce the result you're after: SELECT * FROM tmp t1 WHERE NOT EXISTS(SELECT TRUE FROM tmp t2 WHERE t2.b=t1.a); HTH Ian Lawrence Barwick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
