shud use NOT EXISTS instead of NOT IN as others have suggested .
becoz NOT IN is not very efficient at the moment except the case on there are small number of items in IN( ... ) regds mallah. On Wednesday 12 Mar 2003 2:16 am, Terry Lee Tucker wrote: > I answered my own question. Yes, it can be done like this: > > SELECT code, name, city, country, province FROM cust WHERE (country, > province) NOT IN > (SELECT country, code FROM province); > > The query returned two cust records that had bogus province codes in > them. Very cool :o) > > Terry Lee Tucker wrote: > > > I have loaded over 29,000 customer records into a database. I'm trying > > to apply a referential foreign key that validates the country code and > > the province code in another table. It won't work because somewhere in > > that 29,000 records there is a province code or country code that > > doesn't match. Is there a way to use a select statement to find out > > which customer records has the invalid data? Here is the partial table > > layout: > > > > cust province > > -------- ------- > > country ===> country > > province ===> code > > > > Thanks in advance... > > > > -- > Sparta, NC 28675 USA > 336.372.6812 > http://www.esc1.com > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > -- Regds Mallah ---------------------------------------- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
