Home Adding data from the second table to the first matching record in first table and setting the rest to zero

Questions

Adding data from the second table to the first matching record in first table and setting the rest to zero

April 20, 2023

I’m trying to join two tables in SQL Server as below:
(these are sample tables and the actual one has thousands of records)

Sales table

ID	SALESID	ITEM
1	S1	Item1
2	S1	Item2
3	S1	Item3
4	S2	Item 4
5	S3	Item 5
6	S3	Item 6

Invoice table

INVSALESID	Invoice amount
S1	A
S2	B
S3	C
S4	D
S5	E
S6	F

I’m hoping to get the invoice amount added to the first record and rest as 0 to avoid duplication when I am taking the sum for further analysis.

Current result:

Select s.*,Invoice_amount 

FROM Sales_table s
join Invoice_table inv on  s.salesid=inv.InvSalesId
where s.ID = (
       select top 1 s.ID  FROM Sales_table s1
       WHERE S.SALESID=S1.SALESID
          
      )

ID	SALESID	ITEM	Invoice amount
1	S1	Item1	A
2	S1	Item2	A
3	S1	Item3	A
4	S2	Item 4	B
5	S3	Item 5	C
6	S3	Item 6	C

Expected result:

ID	SALESID	ITEM	Invoice amount
1	S1	Item1	A
2	S1	Item2	0
3	S1	Item3	0
4	S2	Item 4	B
5	S3	Item 5	C
6	S3	Item 6	0

>Solution :

Something like this perhaps:

select id, SALESID, item, [Details…], case when row_number() over(partition by salesid order by id) = 1 then [invoice amount] else 0 end
from (
    VALUES  (1, N'S1', N'Item1', NULL)
    ,   (2, N'S1', N'Item2', NULL)
    ,   (3, N'S1', N'Item3', NULL)
    ,   (4, N'S2', N'Item 4', NULL)
    ,   (5, N'S3', N'Item 5', NULL)
    ,   (6, N'S3', N'Item 6', NULL)
) t (ID,SALESID,ITEM,[Details…])
inner join (
    VALUES  (N'S1', 10)
    ,   (N'S2', 20)
    ,   (N'S3', 30)
    ,   (N'S4', 40)
    ,   (N'S5', 50)
    ,   (N'S6', 60)
) t2 (NVSALESID,[Invoice amount])
    ON  t2.nvsalesid = t.SALESID

you partition by salesid and check if it’s first row then return amount, otherwise 0