CODEWITHSUNDEEP
sqlsql-server
Ryan Johnson
Ryan Johnson

Reputation: 105

Retrieving a list of items with duplicate

I'm attempting to retrieve a list of invoices that have multiple line items with the same Item IDS. There is no specific item I'm looking for I'm just looking to find a way to retrieve rows from the invoice detail table that contains the same invoice number as the invoice table, AND has multiple rows of the same items in the invoice detail table. It seems a little hard to describe so hopefully that made sense but any suggestions are greatly appreciated!! Thanks guys.

These subqueries aren't correct but hopefully it'll give some insight into what i'm going for:

SELECT Invoice.InvNum, Invoice.Invdate, Invoice.Description
FROM Invoice 
INNER JOIN InvDetail ON (Invoice.InvNum = InvDetail.InvoiceNumber) 
WHERE Invoice.InvDate > '7/3/2018' AND ItemID NOT IN  
      (SELECT ItemID FROM InvDetail 
       GROUP BY ItemID HAVING COUNT(DISTINCT ItemID) > 1)
ORDER BY InvDetail.InvoiceNumber


SELECT Invoice.InvNum, Invoice.Invdate, Invoice.Description
FROM Invoice 
INNER JOIN InvDetail ON (Invoice.InvNum = InvDetail.InvoiceNumber) 
WHERE Invoice.InvDate > '7/3/2018' AND InvDetail.InvoiceNumber IN  
     (SELECT InvoiceNumber FROM InvDetail 
      WHERE InvDetail.ItemID NOT IN 
           (SELECT ItemID FROM InvDetail GROUP BY ItemID HAVING COUNT (itemid) > 1))
ORDER BY Invoice.InvNum

Upvotes: 0

Views: 54

Answers (1)

sticky bit
sticky bit

Reputation: 37477

Your subqueries aren't correlated regarding the invoice and the JOIN might cause multiple rows for the same invoice to be retrieved.

One solution, as far as I understood the question, is a correlated, aggregating subquery with EXISTS.

SELECT i.invnum,
       i.invdate,
       i.description
       FROM invoice i
       WHERE i.invdate > '7/3/2018'
             AND EXISTS (SELECT 1
                                FROM invoicedetail id
                                WHERE id.invoicenumber = i.invnum
                                GROUP BY id.itemid
                                HAVING count(id.itemid) > 1);

Upvotes: 2

Related Questions