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@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Owen Hartnett
> Sent: Thursday, May 22, 2008 11:22 AM
> To: pgsql-general@postgresql.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general