On 2004-12-02, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin B." <db@ke5in.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