Message-ID: <3E6E4E1B.2080605@joeconway.com>
Date: Tue, 11 Mar 2003 12:59:07 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.2.1) Gecko/20021130
MIME-Version: 1.0
To: Terry Lee Tucker <terry@esc1.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Novice needs help
References: <3E6E2139.5060903@esc1.com>
In-Reply-To: <3E6E2139.5060903@esc1.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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