SQL Server Performance

Query Multiple Fields

Discussion in 'General Developer Questions' started by vimalpercy, Feb 19, 2010.

  1. vimalpercy New Member

    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.
  2. Adriaan New Member

    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.
  3. vimalpercy New Member

    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!
  4. Adriaan New Member

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

Share This Page