Display all unmatching records based on zip code

[mark1110]

I have two tables, addr_list and usps_zip. I would like to find all records in addr_list that have an incorrect county name based on the zip code when I compare zip code to the zip code in the usps_zip table. I tried

SELECT * FROM ADDR_LIST WHERE ADDR_LIST.COUNTY NOT IN (SELECT COUNTY FROM USPS_ZIP WHERE USPS_ZIP.ZIP = ADDR_LIST.ZIP)

but all I get are all the records in addr_list. I must be missing something simple. Can someone tell me what I am doing wrong?

Thanks,

Mark

 

[PHV]

Perhaps something like this ?
SELECT A.*
FROM ADDR_LIST A LEFT JOIN USPS_ZIP U ON A.ZIP = U.ZIP
WHERE U.ZIP IS NULL OR TRIM(UPPER(A.COUNTY)) <> TRIM(UPPER(U.COUNTY))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[james555]

sounds like firefly would work for you but you probably solved this problem by now.

http://www.getfirefly.net/