pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfind the "missing" rows
3+ messages / 3 participants
[nested] [flat]
* find the "missing" rows
@ 2004-12-01 15:43 Kevin B. <db@ke5in.com>
0 siblings, 1 reply; 3+ messages in thread
From: Kevin B. @ 2004-12-01 15:43 UTC (permalink / raw)
To: pgsql-sql
I would like to find the "missing" rows between two sets without using a
subselect (or views).
This query finds the rows that are in t1 but not in t2. (see the script
below for table definitions.)
QUERY 1:
--------
select * from t1 left join t2 on t1.i = t2.i where t2.i is null
The above query is across two tables. I'm having trouble when all the
data is in the same table. Here is my attempt but it does not work. I've
thought about doing it with views but I really would like to try without
views or a subselect...
QUERY 2:
--------
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
Is there some clever trick get a query working in similar fashion to QUERY
1 but when all the data is in the same table (as in table "t")?
Definitions for Query 1
------------------------
create table t1 (i int);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
insert into t1 values(4);
insert into t1 values(5);
create table t2 (i int);
insert into t2 values(1);
insert into t2 values(2);
insert into t2 values(3);
insert into t2 values(5);
Definitions for Query 2
------------------------
create table t (n varchar(10), i int);
insert into t values('a',1);
insert into t values('a',2);
insert into t values('a',3);
insert into t values('a',4);
insert into t values('a',5);
insert into t values('b',1);
insert into t values('b',2);
insert into t values('b',3);
insert into t values('b',5);
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: find the "missing" rows
@ 2004-12-02 01:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin B. <db@ke5in.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-12-02 01:53 UTC (permalink / raw)
To: db@ke5in.com; +Cc: pgsql-sql
"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 ...
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: find the "missing" rows
@ 2004-12-02 10:07 Andrew - Supernews <andrew+nonews@supernews.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Andrew - Supernews @ 2004-12-02 10:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-12-02 10:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-01 15:43 find the "missing" rows Kevin B. <db@ke5in.com>
2004-12-02 01:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-12-02 10:07 ` Andrew - Supernews <andrew+nonews@supernews.com>
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