Finding records that are not there

From: Owen Hartnett <owen(at)clipboardinc(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Finding records that are not there
Date: 2008-05-22 16:21:35
Message-ID: p06240802c45b4d152861@[192.168.0.100]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adam Rich 2008-05-22 16:46:52 Re: Finding records that are not there
Previous Message Steve Atkins 2008-05-22 16:07:23 Re: Installed pgadmin3-1.4.3 with 8.3.1 database..errors