CODEWITHSUNDEEP
sqlsql-server
user1111928
user1111928

Reputation: 165

How to sort data before joining

Trying to do a diff of 2 tables with same data layout and have mismatch records on sameline

Table1                   Table2
------                   ------

CUSTOMER PART            CUSTOMER  PART

CUSTID1  PART1           CUSTID1  PART1
CUSTID1  PART2           CUSTID1  PART2
CUSTID1  PART3           CUSTID1  PART3
CUSTUD1  PART4


SELECT T1.CUSTID, T1.PART, T2.CUSTID, T2.PART FROM TABLE1 AS T1
LEFT JOIN TABLE2 AS T2 ON T1.CUSTID = T2.CUSTID
WHERE  T1.PART <>  T2.PaRT
ORDER BY T1.CUSTID

Sometimes the output look like below - it shows a mismatch when there isn't one. CAn I order the tables before the join to get the data to line up ?

Thanks

CUSTID1  PART1           CUSTID1  PART3
CUSTID1  PART2           CUSTID1  PART1
CUSTID1  PART3           CUSTID1  PART2
CUSTUD1  PART4           NULL     NULL

Upvotes: 0

Views: 6477

Answers (2)

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

You need to do something like:

SELECT T1.CUSTID, T1.PART, T2.CUSTID, T2.PART 
FROM TABLE1 AS T1 
     INNER JOIN TABLE2 AS T2 ON T1.CUSTID = T2.CUSTID 
WHERE T1.PART <> T2.PaRT

There's no need to use left outer join. The order by clause doesn't make any difference in result of join; it just orders the result.

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 753970

Your current query, even on the sample data, should be giving you lots of data:

SELECT T1.CUSTID, T1.PART, T2.CUSTID, T2.PART FROM TABLE1 AS T1
LEFT JOIN TABLE2 AS T2 ON T1.CUSTID = T2.CUSTID
WHERE  T1.PART <>  T2.PaRT
ORDER BY T1.CUSTID

Expected result:

CUSTID1    PART1    CUSTID1    PART2
CUSTID1    PART1    CUSTID1    PART3
CUSTID1    PART2    CUSTID1    PART1
CUSTID1    PART2    CUSTID1    PART3
CUSTID1    PART3    CUSTID1    PART1
CUSTID1    PART3    CUSTID1    PART2
CUSTID1    PART4    CUSTID1    PART1
CUSTID1    PART4    CUSTID1    PART2
CUSTID1    PART4    CUSTID1    PART3

You probably want a FULL OUTER JOIN with the condition ON T1.CustID = T2.CustID AND T1.Part = T2.Part. If you can't do FULL, use a UNION of LEFT and RIGHT joins. Or if you're not worried about rows in Table2 with no counterpart in Table1, just your existing LEFT JOIN with the enhanced ON clause will do.

If you want all the matched rows and all mismatched rows, then you use

SELECT T1.CustID, T1.Part, T2.CustID, T2.Part
  FROM Table1 AS T1
  FULL OUTER JOIN Table2 AS T2
       ON T1.CustID = T2.CUSTID AND T1.Part = T2.Part

If you only want to see only the mismatched rows, you write:

SELECT T1.CustID, T1.Part, T2.CustID, T2.Part
  FROM Table1 AS T1
  FULL OUTER JOIN Table2 AS T2
       ON T1.CustID = T2.CUSTID AND T1.Part = T2.Part
 WHERE T1.Part IS NULL OR T2.Part IS NULL

The LEFT-UNION-RIGHT query (for all rows) might be:

SELECT T1.CustID, T1.Part, T2.CustID, T2.Part
  FROM Table1 AS T1
  LEFT OUTER JOIN Table2 AS T2
       ON T1.CustID = T2.CUSTID AND T1.Part = T2.Part
UNION
SELECT T1.CustID, T1.Part, T2.CustID, T2.Part
  FROM Table1 AS T1
 RIGHT OUTER JOIN Table2 AS T2
       ON T1.CustID = T2.CUSTID AND T1.Part = T2.Part

And the LEFT only query for all rows might be:

SELECT T1.CustID, T1.Part, T2.CustID, T2.Part
  FROM Table1 AS T1
  LEFT OUTER JOIN Table2 AS T2
       ON T1.CustID = T2.CUSTID AND T1.Part = T2.Part

You can add an appropriate WHERE filter condition if you only want to see the mismatched rows. (Obviously, if you only want to see the matched rows, you use a regular INNER JOIN or just JOIN.)

Caveat Emptor: No DBMS has validated this SQL.

Upvotes: 0

Related Questions