Hi
2011/5/15 Seb <spluque@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 t1WHERE NOT EXISTS(SELECT TRUE FROM tmp t2 WHERE t2.b=t1.a);
HTH
Ian Lawrence Barwick