From: Kevin B. <db@ke5in.com>
To: pgsql-sql@postgresql.org
Subject: find the "missing" rows
Date: Wed, 1 Dec 2004 10:43:30 -0500 (EST)
Message-ID: <33080.67.87.27.161.1101915810.squirrel@www.ke5in.com> (raw)
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);
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: db@ke5in.com
Subject: Re: find the "missing" rows
In-Reply-To: <33080.67.87.27.161.1101915810.squirrel@www.ke5in.com>
* 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