From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Terry Lee Tucker <terry@esc1.com>
To: Terry Lee Tucker <terry@turbocorp.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Novice needs help
Date: Wed, 12 Mar 2003 10:25:10 +0530
Message-ID: <200303121025.10999.mallah@trade-india.com> (raw)
In-Reply-To: <3E6E4B16.4060700@esc1.com>
References: <3E6E2139.5060903@esc1.com>
<3E6E4B16.4060700@esc1.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah@trade-india.com, terry@esc1.com, terry@turbocorp.com
Subject: Re: Novice needs help
In-Reply-To: <200303121025.10999.mallah@trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox