Find duplicate values by joining tables SQL server

Question

Finding the duplicate values in the 'Item_Sales_Detail' table as NULL rows in the 'Sales' and 'Item' tables by joining three tables.

'Sales' table (ID is primary key)

ID	Invoice	Date	TotalAmount
10	00000000100001	02/02/2023	2000
20	00000000100002	02/02/2023	1500
30	00000000100003	02/02/2023	18000

'Items' table (Sales_ID foreign key)

ID	Sales_ID	Item_Code	Amount	Quantity	Total_Amount
1	10	22	2000	1	2000
2	20	35	1500	1	1500
3	30	44	5000	2	10000
4	30	14	8000	1	8000

'Item_Sales_Detail' table (Sales_ID , Item_ID , Invoice are foreign keys)

ID	Sales_ID	Item_ID	invoice	date	Amount
1	10	1	00000000100001	02/02/2023	2000
2	10	1	00000000100001	02/02/2023	2000
3	20	2	00000000100002	02/02/2023	1500
4	30	3	00000000100003	02/02/2023	5000
5	30	3	00000000100003	02/02/2023	5000
6	30	3	00000000100003	02/02/2023	5000
7	30	4	00000000100003	02/02/2023	8000

In table "Item_Sales_Detail," invoice number 00000000100001 has 1 extra record as a duplicate, and invoice number 00000000100003 with Item_ID 3 has quantity 2 and an extra record entered; the total is now 3 records instead of 2.

My query :

SELECT Sales.Invoice,
       Items.Item_Code,
       Item_Sales_Detail.invoice,
       Item_Sales_Detail.date,
       Item_Sales_Detail.Amount
FROM Sales
INNER JOIN Items ON Sales.ID=Items.Sales_ID
INNER JOIN Item_Sales_Detail Items.ID= Item_Sales_Detai.Item_ID

Result

Sales.Invoice	Items.Item_Code	Item_Sales_Detail.invoice	Item_Sales_Detail.date	Item_Sales_Detail.Amount
00000000100001	22	00000000100001	02/02/2023	2000
00000000100001	22	00000000100001	02/02/2023	2000
00000000100002	35	00000000100002	02/02/2023	1500
00000000100003	44	00000000100003	02/02/2023	5000
00000000100003	44	00000000100003	02/02/2023	5000
00000000100003	44	00000000100003	02/02/2023	5000
00000000100003	14	00000000100003	02/02/2023	8000

Expected :

Sales.Invoice	Items.Item_Code	Item_Sales_Detail.invoice	Item_Sales_Detail.date	Item_Sales_Detail.Amount
00000000100001	22	00000000100001	02/02/2023	2000
NULL	NULL	00000000100001	02/02/2023	2000
00000000100002	35	00000000100002	02/02/2023	1500
00000000100003	44	00000000100003	02/02/2023	5000
00000000100003	44	00000000100003	02/02/2023	5000
NULL	NULL	00000000100003	02/02/2023	5000
00000000100003	14	00000000100003	02/02/2023	8000

score 2 · Answer 1 · answered Feb 17 '23 at 04:11

Enumerate each instance of an item and match it against Items.Quantity. Replace Sales.Invoice and Items.Item_Code with nulls where the number exceeds the quantity:

SELECT
  Invoice   = IIF(ROW_NUMBER() OVER (PARTITION BY isd.Item_ID ORDER BY isd.ID ASC) > i.Quantity, NULL, s.Invoice),
  Item_Code = IIF(ROW_NUMBER() OVER (PARTITION BY isd.Item_ID ORDER BY isd.ID ASC) > i.Quantity, NULL, i.Item_Code),
  isd.invoice,
  isd.date,
  isd.Amount
FROM
  dbo.Sales AS s
  INNER JOIN dbo.Items AS i ON s.ID = i.Sales_ID
  INNER JOIN dbo.Item_Sales_Detail AS isd ON i.ID = isd.Item_ID
;

Find duplicate values by joining tables SQL server

1 Answers1