Re: find the "missing" rows

On 2004-12-02, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin B." <db(at)ke5in(dot)com> writes:
>> Select a.i, b.i
>> from t as a
>> left join t as b on a.i = b.i
>> where a.n = 'a' and b.n = 'b' and b.i is null
>
> This can't succeed since the b.n = 'b' condition is guaranteed to fail
> when b.* is nulled out ...

You can make it work by moving parts of the condition into the explicit
join clause:

select a.i
from t as a left join t as b on a.n='a' and b.n='b' and a.i=b.i
where a.n='a' and b.i is null;

(notice you still need the check on a.n='a' outside the join condition)

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services