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.