none
t-sql help with joins and matching rows onyl once

    Question

  • Hi,I've got a bit of a homework question and I'm a little stumped.

    I have 2 simple tables

    Table1

    id        BuySell Quantity

    A34     B          500

    A35     B          500

    A36     S          100

    Table2

    id         BuySell    Quantity

    B16      B             500

    B17      S             100

    B18      B             500

    I need a sql query query that will return the following output

    T1Id,   T2Id

    A34     B16

    A35    B18

    A36   B17

    Basically the requirement is I match joins based on all NON primary keys (id) so BuySell and quantity must match in both rows. The tricky part for me is that I can only match a row from each table once 

    so to return A34 B18 is invalid because both records have already been matched.

    Thanks in advance for any ideas or pointers and I hope it makes sense.

    Thursday, September 26, 2013 1:14 AM

Answers

  • Use ranking functions to add unique identifier for identical rows.

    WITH T1 AS
    
    (
    
    SELECT id, BuySell, Quantity,
    
    ROW_NUMBER() OVER (PARTITION BY BuySell, Quantity ORDER BY id) AS RN
    
    FROM Table1
    
    ),
    T2 AS
    
    (
    
    SELECT id, BuySell, Quantity,
    
    ROW_NUMBER() OVER (PARTITION BY BuySell, Quantity ORDER BY id) AS RN
    
    FROM Table2
    
    ),
    
    SELECT
    
    T1.id,T2.id
    
    FROM T1 FULL OUTER JOIN T2 ON T1.BuySell = T2.BuySell
    
    AND T1.Quantity = T2.Quantity
    
    AND T1.RN = T2.RN







    • Edited by Piotr Palka Thursday, September 26, 2013 2:18 AM
    • Marked as answer by Pritesh3 Thursday, September 26, 2013 1:24 PM
    Thursday, September 26, 2013 2:17 AM
  • DECLARE @tabA TABLE(Id VARCHAR(3), BuySell CHAR(1), Quantity INT)
    
    INSERT INTO @tabA( Id, BuySell, Quantity )
    VALUES  ('A34','B',500),
    ('A35','B',500),
    ('A36','S',100)
    
    SELECT *
    FROM @tabA
     
    DECLARE @tabB TABLE(Id VARCHAR(3), BuySell CHAR(1), Quantity INT)
    
    INSERT INTO @tabB( Id, BuySell, Quantity )
    VALUES  ('B16','B',500),
    ('B17','S',100),
    ('B18','B',500)
    
    SELECT *
    FROM @tabB
    
    --Result
    ;WITH cte AS 
    (
    SELECT a.Id AS TAId
    	,b.Id AS TBId
    	,ROW_NUMBER() OVER (PARTITION BY a.Id ORDER BY a.Id) AS Rna
    	,ROW_NUMBER() OVER (PARTITION BY b.Id ORDER BY a.Id) AS Rnb
    FROM @tabA AS A
    CROSS APPLY @tabB b
    WHERE a.BuySell = b.BuySell	
    	AND a.Quantity = b.Quantity
    )
    SELECT TAId
    	,TBId
    FROM cte
    WHERE Rna = Rnb
    ORDER BY TAId


    Narsimha

    • Marked as answer by Pritesh3 Thursday, September 26, 2013 1:25 PM
    Thursday, September 26, 2013 2:28 AM

All replies

  • Use ranking functions to add unique identifier for identical rows.

    WITH T1 AS
    
    (
    
    SELECT id, BuySell, Quantity,
    
    ROW_NUMBER() OVER (PARTITION BY BuySell, Quantity ORDER BY id) AS RN
    
    FROM Table1
    
    ),
    T2 AS
    
    (
    
    SELECT id, BuySell, Quantity,
    
    ROW_NUMBER() OVER (PARTITION BY BuySell, Quantity ORDER BY id) AS RN
    
    FROM Table2
    
    ),
    
    SELECT
    
    T1.id,T2.id
    
    FROM T1 FULL OUTER JOIN T2 ON T1.BuySell = T2.BuySell
    
    AND T1.Quantity = T2.Quantity
    
    AND T1.RN = T2.RN







    • Edited by Piotr Palka Thursday, September 26, 2013 2:18 AM
    • Marked as answer by Pritesh3 Thursday, September 26, 2013 1:24 PM
    Thursday, September 26, 2013 2:17 AM
  • DECLARE @tabA TABLE(Id VARCHAR(3), BuySell CHAR(1), Quantity INT)
    
    INSERT INTO @tabA( Id, BuySell, Quantity )
    VALUES  ('A34','B',500),
    ('A35','B',500),
    ('A36','S',100)
    
    SELECT *
    FROM @tabA
     
    DECLARE @tabB TABLE(Id VARCHAR(3), BuySell CHAR(1), Quantity INT)
    
    INSERT INTO @tabB( Id, BuySell, Quantity )
    VALUES  ('B16','B',500),
    ('B17','S',100),
    ('B18','B',500)
    
    SELECT *
    FROM @tabB
    
    --Result
    ;WITH cte AS 
    (
    SELECT a.Id AS TAId
    	,b.Id AS TBId
    	,ROW_NUMBER() OVER (PARTITION BY a.Id ORDER BY a.Id) AS Rna
    	,ROW_NUMBER() OVER (PARTITION BY b.Id ORDER BY a.Id) AS Rnb
    FROM @tabA AS A
    CROSS APPLY @tabB b
    WHERE a.BuySell = b.BuySell	
    	AND a.Quantity = b.Quantity
    )
    SELECT TAId
    	,TBId
    FROM cte
    WHERE Rna = Rnb
    ORDER BY TAId


    Narsimha

    • Marked as answer by Pritesh3 Thursday, September 26, 2013 1:25 PM
    Thursday, September 26, 2013 2:28 AM
  • Thanks for both your responses. Both were very helpful
    Thursday, September 26, 2013 1:24 PM