I have two tables. A sales table containing invoice numbers, part numbers, and quantities and a Bundles table, Containing a bundle id, part numbers and quantities. E.g:
Sales:
Invoice_No | Part_No | QTY
-----------------------------
1 |aaa |1
1 |bbb |2
1 |ccc |1
2 |aaa |1
2 |ccc |1
2 |ddd |2
3 |aaa |1
3 |bbb |1
3 |ccc |1
Bundles:
BID | Part_No | QTY
-------------------
1 |aaa |1
1 |bbb |2
1 |ccc |1
2 |aaa |1
2 |ccc |1
2 |ddd |1
I want a query to identify invoices that contain all of the parts on a given bundle with at least the required quantities.
i.e. Invoice 001 contains bundle 1 and invoice 002 contains bundle 2
I've been able to get part of the way there by looking at the examples here: https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/
SELECT S.Invoice_No, 1 as Bundle From Sales as S
INNER JOIN (SELECT BID, Part_No, QTY FROM Bundles WHERE BID=1) as B
ON S.Part_No=B.Part_No
GROUP BY S.Invoice_No
HAVING COUNT(S.Part_No)=(SELECT count(Part_No) FROM Bundles WHERE BID=1)
However this query incorrectly identifies invoice 3 as having bundle 1. I'd also like to not have to come up with a separate query for each bundle as that makes it time consuming to add more bundles in the future.
As an extension I'd also like to be able to identify an invoice that contains more than one bundle (e.g. an invoice might contain 2 bundle 1s, or 1 bundle 1 and 1 bundle 2).
I'm using MS ACCESS for this data.
The following should do, based on you data set (unless I have some fields names wrong)
SELECT S.Invoice_No, B.BID, count(S.part_no)
FROM SALES as S
INNER JOIN Bundle as B ON S.Part_No = B.Part_No AND S.qty >= B.qty
GROUP BY S.INVOICE_no, B.BID
HAVING COUNT(S.Part_No) >= (SELECT count(B2.Part_No)
FROM Bundle B2
WHERE B2.BID = B.BID)
The above will work for the simple case when you expect no more than one Bundle per Sale. If an extra item was added to Invoice 1 (ie a single 'ddd'), then this would calculate that there is enough on the same parts to satisfy Bundle 1 & Bundle 2 - because it is not exclusive of the other bundle.
Example:
INSERT INTO SALES VALUES (1, 'aaa', 1); -- satisfies Bundle 1 and Bundle 2
INSERT INTO SALES VALUES (1, 'bbb', 2); -- satisfies Bundle 1
INSERT INTO SALES VALUES (1, 'ccc', 1); -- satisfies Bundle 1 and Bundle 2
INSERT INTO SALES VALUES (1, 'ddd', 1); -- satisfies Bundle 2
But in order to truly satisfy BOTH Bundles there should be 2 of each 'aaa' and 'ccc'. If this level of detail is required, then additional criteria would be required. If not, then don't make it more complex than it needs to be. Here is the SQL Fiddle containg the additional row.