June 20, 2007 at 6:49 pm
I have two tables. A Client and a State.
In the Client table, some of the entries for State codes are corrupted. Like I have a value 'ZZ' which doesn't have a corresponding entry in the State table. How do I find out such entries?
June 20, 2007 at 7:14 pm
SELECT *
FROM Client
WHERE StateCode NOT IN
(SELECT StateCode FROM State)
June 21, 2007 at 8:06 am
Thx. It works for a single key. But not for a composite key. If I have City and Zip in Client and in State, and I try:
SELECT *
FROM Client
WHERE StateCode NOT IN
(SELECT StateCode FROM State)
AND
city NOT IN
(SELECT city FROM State)
AND
zip NOT IN
(SELECT zip FROM State)
June 21, 2007 at 7:38 pm
SELECT *
FROM Client A
WHERE NOT EXISTS
(SELECT * FROM State Z
WHERE Z.StateCode = A.StateCode
AND Z.city = A.city
AND Z.zip = A.zip)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply