Scribbly Scribbly - 1 month ago 21
SQL Question

SQL Server Join two tables without duplicates in primary table

I have two tables that I want to join together such that all foreign rows are returned and the primary table's rows are not duplicated. For example:

T1

pk code value
1 One 100
2 Two 200

T2

fk value
1 10
1 15
1 30
2 25


I want all records of T2 without the T1 records duplicating, so the result set I want to look like this:

T2.fk T1.code T1.value T2.value
1 One 100 10
1 NULL NULL 15
1 NULL NULL 30
2 Two 200 25


Is there a SQL Server join method for achieving that?

Answer

You need to rank your rows in T2 and do a left join including rank as a join condition:

with cte as(select *, row_number() over(partition by fk order by value) as rn from T2)

select c.fk, t.code, t.value, c.value 
from cte c
left join T1 t on c.fk = t.pk and c.rn = 1

Here is the full example:

DECLARE @t1 TABLE
    (
      pk INT ,
      code VARCHAR(MAX) ,
      value INT
    )
INSERT  INTO @t1
VALUES  ( 1, 'One', 100 ),
        ( 2, 'Two', 200 )

DECLARE @t2 TABLE ( fk INT, value INT )
INSERT  INTO @t2
VALUES  ( 1, 10 ),
        ( 1, 15 ),
        ( 1, 30 ),
        ( 2, 25 );
WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY fk ORDER BY value ) AS rn
               FROM     @t2
             )
    SELECT  c.fk ,
            t.code ,
            t.value ,
            c.value
    FROM    cte c
            LEFT JOIN @t1 t ON c.fk = t.pk
                               AND c.rn = 1