Date: Tue, 11 Mar 2003 22:03:20 +0100
To: Terry Lee Tucker <terry@esc1.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Novice needs help
Message-ID: <20030311210320.GA4634@serwer>
References: <3E6E2139.5060903@esc1.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3E6E2139.5060903@esc1.com>
User-Agent: Mutt/1.3.28i
From: jasiek@klaster.net

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