Re: Finding records that are not there

You have commcost in the correlated subquery which shouldn't be there.

Here are three ways to achieve the results you want:

select commcost.maplot,
commcost.unitno
from commcost
where not exists(select null
from bldg
where commcost.maplot = bldg.maplot
and commcost.unitno = bldg.unitno)
order by commcost.maplot;

select commcost.maplot,
commcost.unitno
from commcost
left join bldg
on commcost.maplot = bldg.maplot
and commcost.unitno = bldg.unitno
where bldg.maplot is null
order by commcost.maplot;

select commcost.maplot,
commcost.unitno
from commcost
except
select bldg.maplot,
bldg.unitno
from bldg
order by maplot;

Jon

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Owen Hartnett
> Sent: Thursday, May 22, 2008 11:22 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Finding records that are not there
>
>
> Hi:
>
> This is gotta be elementary SQL 101, but I'm having a mental block as
> to why this doesn't work.
>
> I have two tables that have identical index fields, maplot and
> unitno, (both indexes span two columns) and I want to find all the
> records in the commcost table that don't have a corresponding record
> in the bldg file.
>
> The SQL I've tried is:
>
> select commcost.maplot, commcost.unitno from commcost
> where not exists(select 1 from commcost, bldg
> where commcost.maplot = bldg.maplot and
> commcost.unitno = bldg.unitno)
> order by commcost.maplot
>
> It returns no records although I know that there are records in
> commcost which do not match keys with records from bldg.
>
> Help! What am I doing wrong?
>
> -Owen
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general