SQL Relational Division with additional criteria

Question

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.

Your query uses a field called "BID", but there is no such field on your bundles table. There is a field called "ID". Which one is correct? — Brian DeMilia, Aug 29 '14 at 04:19
How do you know invoice 001 contains bundle 1? Is the BundleId related to the InvoiceId? Either the bundle table is replicating information from the invoice table or there is another table which joins these 2 object (or should be if there isn't) — simo.3792, Aug 29 '14 at 04:20
I am guessing the actual way to identify if an invoice is related to a particular bundle is that the bundle contains all matching part_no values of the invoice? Although that is far and away from what your current query is doing. — Brian DeMilia, Aug 29 '14 at 04:21
Based on what you've provided, I would suggest that everything from Bundle 1 is on invoice 3, and therefore maybe it's working correctly... — simo.3792, Aug 29 '14 at 04:23
@BrianDeMilia I corrected the table with BID, I wrote the question before I created a dummy database with the info and query. My real tables are obviously a bit more complex — Ben Baker, Aug 29 '14 at 04:52
@simo.3792095 Basically I want to reward a rep if an invoice contains all of the parts on a bundle. Invoice 3 only contains 1xbbb instead of the required 2xbbb so it should be excluded. That's the bit I can't figure out how to do — Ben Baker, Aug 29 '14 at 04:54
Standard solution for this kind of problem is a `a where NOT EXISTS(b where NOT EXISTS (b*a WHERE ... AND additional criteria))` — joop, Aug 29 '14 at 08:24

score 1 · Answer 1 · answered Aug 30 '14 at 15:06

-- the data
CREATE TABLE sales
        ( seq SERIAL NOT NULL PRIMARY KEY
        , invoice_no INTEGER NOT NULL
        , part_no CHAR(3) NOT NULL
        , qty INTEGER NOT NULL DEFAULT 0
        );
INSERT INTO sales (invoice_no, part_no, qty) VALUES
 (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 )
        ;

CREATE TABLE bundles
        (seq SERIAL NOT NULL PRIMARY KEY
        , bid INTEGER NOT NULL
        , part_no CHAR(3) NOT NULL
        , qty INTEGER NOT NULL DEFAULT 0
        );

INSERT INTO bundles(bid, part_no, qty) VALUES
 (1, 'aaa' ,1) , (1, 'bbb' ,2) , (1, 'ccc' ,1)
, (2, 'aaa' ,1) , (2, 'ccc' ,1) , (2, 'ddd' ,1)
        ;

SELECT * FROM sales;
SELECT * FROM bundles;


        -- find the orders
SELECT * FROM sales sa
WHERE EXISTS (  -- sales is part of some bundles
                -- find all bundles containing the articles in these orders
        SELECT * FROM bundles bu
        WHERE bu.part_no = sa.part_no
        AND NOT EXISTS ( -- this bundle MUST contain ALL items from this order
                         -- ==>> bundle MUST NOT contain ANY item NOT in this order
                         -- (or in too low qty)
                SELECT *
                FROM bundles xb
                WHERE xb.bid = bu.bid
                AND NOT EXISTS ( -- 
                        SELECT * FROM sales xs
                        WHERE xs.part_no = xb.part_no
                        AND xs.invoice_no = sa.invoice_no
                        AND xs.qty >= xb.qty
                        )
                )
        )
        ;

simo.3792 · Accepted Answer · 2014-08-29T07:01:28.813

0

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)

SQL Fiddle

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.

edited Aug 29 '14 at 07:01

answered Aug 29 '14 at 05:53

simo.3792

2,102
1
17
29

Hmm, doesn't quite work. That query returns BID 1 and 2 for invoice 1, 2 and 3 for invoice 2 and 2 for invoice 3 – Ben Baker Aug 29 '14 at 06:20
The following query works for BID 1, but strangely not for BID 2 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 AND S.QTY>=B.QTY GROUP BY S.Invoice_No HAVING COUNT(S.Part_No)=(SELECT count(Part_No) FROM Bundles WHERE BID=1) – Ben Baker Aug 29 '14 at 06:21
Did you try against the SQL Fiddle or against your data set? – simo.3792 Aug 29 '14 at 06:24
@BenBaker Importantly take a look at the JOIN Syntax for the BUNDLE table. You DON'T need to specify BID=1 anywhere on the initial JOIN. You DO need to link the Inner Count Query to the top level reference for Bundle (ie B2.BID = B.BID). Make sure you are GROUPING BY both Invoice_No and BID. – simo.3792 Aug 29 '14 at 06:34
hmmm, the query works fine on my data set now. No idea what was wrong on Friday, but thanks your query is working great. RE: The additional bundles per sale, that is the next step I need to figure out. It's possible for an invoice to contain both a bundle 1 and a bundle 2, or even contain 2 bundle 2s and a bundle 1 etc... – Ben Baker Sep 01 '14 at 05:00
@BenBaker I thought there might be a little more to uncover. At least this query 'as is' will tell you that 'at least' one bundle applies to this invoice. – simo.3792 Sep 01 '14 at 05:09

SQL Relational Division with additional criteria

2 Answers2