CODEWITHSUNDEEP
sqlsql-server
Ben
Ben

Reputation: 47

SQL Server Table Join - Is it Possible to Iterate Through Potential Matches and Ignore Previously Matched Records

I have a migrated dataset of orders where on migration some of the original products were merged into the same "new product." In the example below Product 1 and 3 were merged into a "New product 1."

When the orders were migrated over, rather than combining product 1 and 3 on an order they were loaded as separate line items just with the same product name. I need to join these two tables and get a 1-1 relationship for the order line items.

My issue is that when I join on (Table2.Legacy_Order_number = Table1.Order_number and Table2.Amount = Table1.Amount) my results include duplication since there are 2 possible matches.

My question is: Since it does not matter which of the two "New Product 1" line items from Table 2 is matched back to Product1 and Product3 from Table 1, Is there a way to iterate through the potential matches and then ignore already matched records?

Current Query:

SELECT
     T1.ID AS Original_Order_Number
    ,T1.Name AS Original_Product
    ,T2.ID AS New_ID
    ,T2.Name AS New_Product
    ,T2.Amount
FROM Table1 T1 
LEFT JOIN Table2 T2
    ON T2.Legacy_Order_number = T1.Order_number 
        AND T2.Amount = T1.Amount

Table 1:

Order Number Name Amount
1 Product1 50.00
1 Product2 100.00
1 Product3 50.00

Table 2:

ID Legacy Order Number Name Amount
19 1 New Product 1 50.00
20 1 New Product 2 100.00
21 1 New Product 1 50.00

Desired Query Result :

Original Order Number Original Product New ID New Product Amount
1 Product1 19 New Product 1 50.00
1 Product3 21 New Product 1 50.00
1 Product2 20 New Product 2 100.00

Upvotes: 0

Views: 530

Answers (1)

Charlieface
Charlieface

Reputation: 71475

You can add a partitioned row-number to each table, then add the row-numbers as a join condition

This means that each row is only joined once to a matching row, while still keeping Order_number and Amount as the main joining condition.

SELECT
     T1.ID AS Original_Order_Number
    ,T1.Name AS Original_Product
    ,T2.ID AS New_ID
    ,T2.Name AS New_Product
    ,T2.Amount
FROM (
    SELECT *,
        rn = ROW_NUMBER() OVER (PARTITION BY Order_number, Amount ORDER BY (SELECT 1))
    FROM Table1
) T1 
LEFT JOIN (
    SELECT *,
        rn = ROW_NUMBER() OVER (PARTITION BY Legacy_Order_number, Amount ORDER BY (SELECT 1))
    FROM Table2
) T2
    ON T2.Legacy_Order_number = T1.Order_number 
        AND T2.Amount = T1.Amount
        AND T2.rn = T1.rn;

Upvotes: 2

Related Questions