Help with SQL

[mark1110]

Hi,

I have two tables (table1 and table2). They both contain two fields (ID and state). What I would like is to list all the ID's in table1 that are either not in table2 or if the ID is in table2 to see if the state in table1 is not in table2 with matching ID's.

For example in table1 you have an ID of 1234 and a state of IL. In table2 if 1234 is not there to list it or if 1234 is in table2 and table2 has 1234 IN, 1234 OK, 1234 OR to list the ID because it does not have 1234 IL.

I have tried several different ways with not luck. Could someone please help me out.

Thanks,

Mark

 

[PHV]

SELECT T1.ID, T1.state
FROM table1 T1 LEFT JOIN table2 T2
ON T1.ID = T2.ID AND T1.state = T2.state
WHERE T2.ID IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

SELECT T1.ID, T1.state, [blue]T2.state[/blue]
FROM table1 T1 LEFT JOIN table2 T2
ON T1.ID = T2.ID
WHERE [blue]COALESCE(T2.state,'') <> T1.state[/blue]



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon