pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (3+ messages)

Message-ID: <slrncqtqbo.2kf6.andrew+nonews@trinity.supernews.net>
Permalink:  ../slrncqtqbo.2kf6.andrew+nonews@trinity.supernews.net/
Also on:    postgresql.org/message-id/slrncqtqbo.2kf6.andrew+nonews@trinity.supernews.net

 · 

reply

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