Re: Finding records that are not there
От | Roberts, Jon |
---|---|
Тема | Re: Finding records that are not there |
Дата | |
Msg-id | 1A6E6D554222284AB25ABE3229A92762E9A3B2@nrtexcus702.int.asurion.com обсуждение исходный текст |
Ответ на | Finding records that are not there (Owen Hartnett <owen@clipboardinc.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: