Re: [GENERAL] Finding records that are not there

> 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general