> 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
