Skip site navigation (1) Skip section navigation (2)

Re: find the "missing" rows

From: Andrew - Supernews <andrew+nonews(at)supernews(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: find the "missing" rows
Date: 2004-12-02 10:07:52
Message-ID: slrncqtqbo.2kf6.andrew+nonews@trinity.supernews.net (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: Andrew MDate: 2004-12-03 09:00:53
Subject: Failed system call was shmget(key=1, size=1155072, 03600).
Previous:From: Richard HuxtonDate: 2004-12-02 09:03:49
Subject: Re: order by problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group