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

• Ronaldo: Please, provide the sample data as CREATE and INSERT statements. See Help me write this query in SQL.
• RonJohn: I find duplicates by using GROUP BY and HAVING COUNT(*) > 1

Answer 1

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
;

• Mohammad Bastan: seems working fine but for item_code 14 with invoice 00000000100003 is shwoing null and it shouldn't be null
• Andriy M: @MohammadBastan: For the item_code 14 in your example, no, it shouldn't, and I don't believe it does return a null there. The implementation is according to the requirements. On the other hand, I do admit that I didn't test my query. If you could provide a test setup for me, I'd be able to tune the query for you.
• Mohammad Bastan: here it is dbfiddle.uk/udPtiNRQ
• Andriy M: @MohammadBastan: Thank you. The dbfiddle setup for which you've posted the link has a small but crucial difference from the setup in your question. That difference is the reason why you are not getting the expected result. In the question, the row ID=7 in Item_Sales_Detail has the Item_ID of 4. But in your dbfiddle setup, table Item_Sales_Detail has no row with Item_ID=4. The row 7 has Item_ID=3, which is correctly returned with nulls. Here's the fixed setup, and the output is as expected. So the code works correctly, the error was in the data.