Query to Find Mismatches

  • MalcolmS

    Valued Member

    Points: 62

    New user with what should be an easy question. I have two tables; both are lists of SKU numbers and prices. I need to write a query which outputs values where the price in Table A doesn't match the price in Table B. The SKUs from A should always be in table B so it's not a "find null" query, which I was able to figure out. Any assistance would be appreciated. I tried using the Find Unmatched Query from the Wizard but it didn't seem to do what I need.

  • Michael L John

    One Orange Chip

    Points: 25632

    SELECT *

    FROM TableA as A

    INNER JOIN TableB as B ON A.SKU = B.SKU

    WHERE A.Price <> B.Price

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Thom A

    SSC Guru

    Points: 98029

    Just in case Price could have the value NULL , you may need to do therefore do:

    SELECT * --You should probably list the columns you actually need here instead
    FROM TableA A
    JOIN TableB B ON A.SKU = B.SKU
    WHERE A.Price <> B.Price
    OR (A.Price IS NULL AND B.Price IS NOT NULL)
    OR (A.Price IS NOT NULL AND B.Price IS NULL);

     

     

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Scott Coleman

    One Orange Chip

    Points: 27309

    If your original query didn't work, you probable have to worry about NULLs.

    An EXCEPT query is simpler to write and will deal with NULLs correctly, but performance depends on rowcounts and indexing.  Check the query plans for Thom's solution and this query, and don't use this one if it does not perform as well.

    SELECT  SKU, price FROM TableA
    EXCEPT
    SELECT SKU, Price FROM TableB

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply