From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Terry Lee Tucker <terry(at)esc1(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Novice needs help |
Date: | 2003-03-11 20:59:07 |
Message-ID: | 3E6E4E1B.2080605@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | jasiek | 2003-03-11 21:03:20 | Re: Novice needs help |
Previous Message | Terry Lee Tucker | 2003-03-11 20:46:14 | Re: Novice needs help |