From: jasiek@klaster.net
To: Terry Lee Tucker <terry@esc1.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Novice needs help
Date: Tue, 11 Mar 2003 22:03:20 +0100
Message-ID: <20030311210320.GA4634@serwer> (raw)
In-Reply-To: <3E6E2139.5060903@esc1.com>
References: <3E6E2139.5060903@esc1.com>
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
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: jasiek@klaster.net, terry@esc1.com
Subject: Re: Novice needs help
In-Reply-To: <20030311210320.GA4634@serwer>
* 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