From: Rajesh Kumar Mallah <mallah@trade-india.com>
Organization: Infocom Network Limited
To: Terry Lee Tucker <terry@esc1.com>, Terry Lee Tucker <terry@turbocorp.com>
Subject: Re: Novice needs help
Date: Wed, 12 Mar 2003 10:25:10 +0530
User-Agent: KMail/1.5
Cc: pgsql-sql@postgresql.org
References: <3E6E2139.5060903@esc1.com> <3E6E4B16.4060700@esc1.com>
In-Reply-To: <3E6E4B16.4060700@esc1.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200303121025.10999.mallah@trade-india.com>


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.