CODEWITHSUNDEEP
sql-serverduplicatesleft-join
Karstieman
Karstieman

Reputation: 57

Join two columns and pick just one from the child results

I want to perform a JOIN on two tables to get the LEADTIME per ITEMNUM. Both tables have common value ITEMNUM that I use for the JOIN operation.

The problem is that in the second table the ITEMNUM is not unique and can contain multiple. different LEADTIME values. For example see ITEMNUM 2 in Table 2.

enter image description here

In case there are multiple LEADTIME values, I just want to get one of the LEADTIME values. I don't care which one.

This is what I have so far, but it keeps returning multiple lines for ITEMNUM 2

SELECT ITEMNUM, LEADTIME
FROM TABLE1
LEFT JOIN TABLE2 on TABLE2.ITEMNUM = TABLE1.ITEMNUM

So what can I do to get just one LEADTIME for ITEMNUM 2? ( as mentioned, I don't care which value )

Upvotes: 0

Views: 33

Answers (2)

Chris Albert
Chris Albert

Reputation: 2507

There are several ways to get this done.I would use OUTER APPLY with TOP.

DROP TABLE IF EXISTS #Table1
CREATE TABLE #Table1
(
    Id INT
    , ItemNum INT
)

DROP TABLE IF EXISTS #Table2
CREATE TABLE #Table2
(
    Id INT
    , ItemNum INT
    , LeadTime INT
)

INSERT INTO #Table1 VALUES 
(1, 1)
, (2, 2)
, (3, 3)
, (4, 4)
, (5, 5)

INSERT INTO #Table2 VALUES 
(1, 1, 6)
, (2, 2, 7)
, (3, 2, 2)
, (4, 3, 6)
, (5, 4, 3)

SELECT
    *
FROM
    #Table1 AS T1
    OUTER APPLY
    (
        SELECT TOP 1 T2.LeadTime FROM #Table2 AS T2 WHERE T2.ItemNum = T1.ItemNum
    ) AS LT

Upvotes: 1

Isaac
Isaac

Reputation: 3363

This approach assigns a row number to each row in #table2 resetting it for each ItemNum value. You need to have an order by clause (if you don't SQL Server raises an error) so I am ordering by NEWID() which should result in a randomized order. You will likely want to tweak what columns you are returning. Here is the dbfiddle to see it in action.

IF OBJECT_ID('tempdb.dbo.#table1', 'U') IS NOT NULL DROP TABLE #table1; 

IF OBJECT_ID('tempdb.dbo.#table2', 'U') IS NOT NULL DROP TABLE #table2; 

CREATE TABLE #table1
(
    ID INT
  , ItemNum INT
);

CREATE TABLE #table2
(
    ID INT
  , ItemNum INT
  , LeadTime INT
);

INSERT INTO #table1 VALUES (1, 1)
INSERT INTO #table1 VALUES (2, 2)
INSERT INTO #table1 VALUES (3, 3)
INSERT INTO #table1 VALUES (4, 4)
INSERT INTO #table1 VALUES (5, 5)

INSERT INTO #table2 VALUES (1, 1, 6)
INSERT INTO #table2 VALUES (2, 2, 7)
INSERT INTO #table2 VALUES (3, 2, 2)
INSERT INTO #table2 VALUES (4, 3, 6)
INSERT INTO #table2 VALUES (5, 4, 3)

SELECT    *
FROM      #table1 AS t1
LEFT JOIN (
              SELECT *
                   , ROW_NUMBER() OVER (PARTITION BY ItemNum ORDER BY NEWID()) AS rn
              FROM   #table2
          )       AS t2 ON t1.ItemNum = t2.ItemNum
                           AND t2.rn = 1;

Upvotes: 1

Related Questions