Thread: 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

On Thu, May 22, 2008 at 12:21:35PM -0400, Owen Hartnett wrote:
> 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

change it to:

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

or simply write:

select * from commcost except select * from bldg;

depesz

At 6:47 PM +0200 5/22/08, hubert depesz lubaczewski wrote:
>On Thu, May 22, 2008 at 12:21:35PM -0400, Owen Hartnett wrote:
>>  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
>
>change it to:
>
>>  select commcost.maplot, commcost.unitno from commcost
>>  where not exists(select 1 from bldg
>>  where commcost.maplot = bldg.maplot and
>>  commcost.unitno = bldg.unitno)
>>  order by commcost.maplot
>
>or simply write:
>
>select * from commcost except select * from bldg;
>
>depesz

Thank you very much for your quick response!

-Owen

> 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.
>

You shouldn't put "commcost" in your inner select, since it's
already in your outer select.

Or try this, it's probably faster:

Select commcost.maplot, commcost.unitno from commcost c
left join bldg b on c.maplot = b.maplot and c.unitno = b.unitno
where b.unitno is null

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