I have this requirement. I have table A which has ID_A_A, ID_B_A and ID_C_A I have table B Which has ID_A_B,ID_B_B,ID_C_B I want to write a query to get items from Table A where ID_A_A<>ID_A_B AND ID_B_A<>ID_B_B AND ID_C_A<>ID_C_B I tried a subquery but it returns all records for each entry in A other than the one specified. So basically what I want is to compare the 3 items in Table A with 3 items in Table B and get all items in TABLE A that are not in TABLE B. Thanks in advance for the quick reply.
Two options (1) a subquery after NOT EXISTS, with a = comparison: SELECT * FROM tableA a WHERE NOT EXISTS (SELECT * FROM tableB b WHERE b.ID_A_B = a.ID_A_A AND b.ID_B_B = a.ID_B_A AND b.ID_C_B = a.ID_C_A) (2) a LEFT JOIN with IS NULL criteria for the "other" table: SELECT * FROM tableA a LEFT JOIN tableB b ON b.ID_A_B = a.ID_A_A AND b.ID_B_B = a.ID_B_A AND b.ID_C_B = a.ID_C_A WHERE b.ID_A_B IS NULL If any of the columns is nullable, and you need to match nulls too, then NOT EXISTS would be my first choice. In that case, write out the criteria like: WHERE NOT EXISTS (SELECT * FROM tableB b WHERE (b.ID_A_B = a.ID_A_A OR (b.ID_A_B IS NULL AND a.ID_A_A IS NULL) AND ............................) I wouldn't use ISNULL() - it may make the criteria easier to understand, but you must be absolutely sure that the replacement value does not exist in the table, otherwise you may well get false matches.
Hello Adriaan You are the man. Thanks a lot for your help. I owe you a dinner at some point for all the help you provide. Have a great weekend!
You're welcome! One small correction to the criteria when matching nulls - I missed a closing bracket, in red: WHERE NOT EXISTS (SELECT * FROM tableB b WHERE (b.ID_A_B = a.ID_A_A OR (b.ID_A_B IS NULL AND a.ID_A_A IS NULL) ) AND ............................)
