1

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
2
  • 1
    Please, provide the sample data as CREATE and INSERT statements. See Help me write this query in SQL.
    – Ronaldo
    Commented Feb 16, 2023 at 21:33
  • 1
    I find duplicates by using GROUP BY and HAVING COUNT(*) > 1;
    – RonJohn
    Commented Feb 16, 2023 at 21:40

1 Answer 1

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
;
4
  • seems working fine but for item_code 14 with invoice 00000000100003 is shwoing null and it shouldn't be null Commented Feb 17, 2023 at 15:54
  • @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.
    – Andriy M
    Commented Feb 17, 2023 at 17:56
  • here it is dbfiddle.uk/udPtiNRQ Commented Feb 18, 2023 at 21:28
  • @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.
    – Andriy M
    Commented Feb 19, 2023 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.