pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNovice needs help
6+ messages / 5 participants
[nested] [flat]
* Novice needs help
@ 2003-03-11 17:47 Terry Lee Tucker <terry@esc1.com>
0 siblings, 4 replies; 6+ messages in thread
From: Terry Lee Tucker @ 2003-03-11 17:47 UTC (permalink / raw)
To: pgsql-sql
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...
--
Terry Lee Tucker
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Novice needs help
@ 2003-03-11 20:46 Terry Lee Tucker <terry@esc1.com>
parent: Terry Lee Tucker <terry@esc1.com>
3 siblings, 1 reply; 6+ messages in thread
From: Terry Lee Tucker @ 2003-03-11 20:46 UTC (permalink / raw)
To: Terry Lee Tucker <terry@turbocorp.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Novice needs help
@ 2003-03-11 20:59 Joe Conway <mail@joeconway.com>
parent: Terry Lee Tucker <terry@esc1.com>
3 siblings, 0 replies; 6+ messages in thread
From: Joe Conway @ 2003-03-11 20:59 UTC (permalink / raw)
To: Terry Lee Tucker <terry@esc1.com>; +Cc: pgsql-sql
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
>
If I understand correctly, something like this should work (not tested):
select c.country, c.province from cust c left join province p on
c.country = p.country and c.province = p.code where p.country is null;
or alternatively
select c.country, c.province from cust c where not exists (select 1 from
province p where p.country = c.country and p.code = c.province);
Joe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Novice needs help
@ 2003-03-11 21:03 jasiek@klaster.net
parent: Terry Lee Tucker <terry@esc1.com>
3 siblings, 0 replies; 6+ messages in thread
From: jasiek@klaster.net @ 2003-03-11 21:03 UTC (permalink / raw)
To: Terry Lee Tucker <terry@esc1.com>; +Cc: pgsql-sql
On Tue, Mar 11, 2003 at 12:47:37PM -0500, 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?
Yes.
Here is the partial table
> layout:
>
> cust province
> -------- -------
> country ===> country
> province ===> code
You didn't notice howw exactly your relation works. Is it:
cust.country=province.country and cust.province=province.code
?
If yes, try this:
select c.country,c.province
from cust c
left join province p on (c.country=p.country and c.province=p.code)
where p.country is null or p.code is null;
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Novice needs help
@ 2003-03-11 21:24 Chad Thompson <chad@weblinkservices.com>
parent: Terry Lee Tucker <terry@esc1.com>
3 siblings, 0 replies; 6+ messages in thread
From: Chad Thompson @ 2003-03-11 21:24 UTC (permalink / raw)
To: Terry Lee Tucker <terry@esc1.com>; pgsql-sql
try something like...
select c.*
from cust c
where not exists
(select p.country
from province p
where p.country = c.country)
This will give you a list of all the countries in cust that are not in
province.. you can do the same with code.
HTH
Chad
----- Original Message -----
From: "Terry Lee Tucker" <terry@esc1.com>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, March 11, 2003 10:47 AM
Subject: [SQL] Novice needs help
> 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...
>
> --
> Terry Lee Tucker
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Novice needs help
@ 2003-03-12 04:55 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Terry Lee Tucker <terry@esc1.com>
0 siblings, 0 replies; 6+ messages in thread
From: Rajesh Kumar Mallah @ 2003-03-12 04:55 UTC (permalink / raw)
To: Terry Lee Tucker <terry@esc1.com>; Terry Lee Tucker <terry@turbocorp.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-03-12 04:55 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-11 17:47 Novice needs help Terry Lee Tucker <terry@esc1.com>
2003-03-11 20:46 ` Terry Lee Tucker <terry@esc1.com>
2003-03-12 04:55 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-11 20:59 ` Joe Conway <mail@joeconway.com>
2003-03-11 21:03 ` jasiek@klaster.net
2003-03-11 21:24 ` Chad Thompson <chad@weblinkservices.com>
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