Thread: 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
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
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
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
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) >
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.