-2

Given a list of ProductID numbers, I need to find details on invoices that contain ALL of the listed ProductID numbers. The number of items in the list is dynamic. Tables look something like this.

Products

ProductID ProductName

Invoices

InvoiceID DateOrdered CustomerID

Invoice_Products

QTY ProductID InvoiceID

Right now I have working MySQL SQL statement but I think performance may become an issue. I wasnt sure if there was a better way to write this:

Select InvoiceId, DateOrdered, CustomerID
from Invoices
where InvoiceID in (
     select InvoiceID from Invoice_Products where ProductID = 5
)
AND 
InvoiceID in (
     select InvoiceID from Invoice_Products where ProductID = 100
)
AND
InvoiceID in (
     select InvoiceID from Invoice_Products where ProductID = 1005
)

My Product List will be provided by a multiple select box of products. Only invoices with all items matched should be displayed else I would have just done an inner join with an in statement.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
if_steve
  • 1

1 Answers1

2

In my experience, JOIN is almost always better than IN subquery.

And you can get all the invoice IDs in a single query.

SELECT i.InvoiceId, i.DateOrdered, i.CustomerID
FROM Invoices i
JOIN (
    SELECT InvoiceID
    FROM Invoice_Products
    WHERE ProductID IN (5, 100, 1005)
    GROUP BY InvoiceID
    HAVING COUNT(DISTINCT ProductID) = 3
) AS ip ON i.InvoiceID = ip.InvoiceID

See How to return rows that have the same column values in MySql for an explanation of the subquery.

Barmar
  • 741,623
  • 53
  • 500
  • 612