From: Andrew - Supernews <andrew+nonews@supernews.com>
To: pgsql-sql@postgresql.org
Subject: Re: find the "missing" rows
Date: Thu, 02 Dec 2004 10:07:52 -0000
Message-ID: <slrncqtqbo.2kf6.andrew+nonews@trinity.supernews.net> (raw)
References: <33080.67.87.27.161.1101915810.squirrel@www.ke5in.com>
<28993.1101952391@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: andrew+nonews@supernews.com
Subject: Re: find the "missing" rows
In-Reply-To: <slrncqtqbo.2kf6.andrew+nonews@trinity.supernews.net>
References: <33080.67.87.27.161.1101915810.squirrel@www.ke5in.com> <28993.1101952391@sss.pgh.pa.us> <slrncqtqbo.2kf6.andrew+nonews@trinity.supernews.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox