Message-ID: <33080.67.87.27.161.1101915810.squirrel@www.ke5in.com>
Date: Wed, 1 Dec 2004 10:43:30 -0500 (EST)
Subject: find the "missing" rows
From: "Kevin B." <db@ke5in.com>
To: pgsql-sql@postgresql.org
Reply-To: db@ke5in.com
User-Agent: SquirrelMail/1.4.2
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Importance: Normal

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);