MySQL Relational Division with multiple IDs

Question

Please take a look at the question described here: MySQL ONLY IN() equivalent clause , regarding Relational Division in MySQL.

My database structure is very similar to the one described, but in the "Chocolate Boys Table", I have an additional ID field - let's call it milk ID.

Chocolates Boys Table

+----+---------+-----------------------+
| id | chocolate_id | milk id | boy_id |
+----+--------------+---------+--------+
| 1  | 1000         | 2000    | 10007  |
| 2  | 1003         | 2001    | 10007  |
| 3  | 1006         | 2005    | 10007  |
| 4  | 1000         | 2001    | 10009  |
| 5  | 1001         | 2000    | 10009  |
| 6  | 1005         | 2008    | 10009  |
+----+--------------+---------+--------|

The objective is to run a query that retrieves the boy ID that contains the exact chocolate and milk IDs that I pass in. Here are some examples of my expected results:

Example #1:

Chocolate IDs Passed In (in order) - 1000,1003,1006.
Milk IDs Passed In (in order) - 2000,2001,2005.
Expected Result: Query returns boy ID of 10007.

Example #2:

Chocolate IDs Passed In (in order) - 1000,1003.
Milk IDs Passed In (in order) - 2000,2001.
Expected Result: Empty result set.

Example #3:

Chocolate IDs Passed In (in order) - 1003,1000,1006.
Milk IDs Passed In (in order) - 2000,2001,2005.

Expected Result: Empty result set - The passed in IDs are included in boy ID 10007, but the order is wrong. The values of Chocolate ID and Milk ID don't match up if examined on a row by row basis.

I am attempting to use a slightly modified version of John Woo's solution in order to incorporate the added ID field:

SELECT boy_id 
FROM   boys_chocolates a
WHERE  chocolate_id IN (1003,1000,1006) AND milk_id IN (2000,2001,2005) AND
    EXISTS 
    (
        SELECT  1
        FROM    boys_chocolates b
        WHERE   a.boy_ID = b.boy_ID
        GROUP   BY boy_id
        HAVING  COUNT(DISTINCT chocolate_id) = 3
    )
GROUP  BY boy_id
HAVING COUNT(*) = 3

The problem that I'm having is that the IN function does not enforce order, as seen in example #3. I would like the above query to return an empty result set. What needs to be changed in order to address this problem? Thank you!

if it was me, I'd forget about the milk and focus on the chocolate. — Strawberry, Feb 01 '18 at 17:27

score 1 · Answer 1 · answered Feb 01 '18 at 17:24

Try this approach:

SELECT a.boy_id
FROM
  (SELECT id, boy_id FROM boys_chocolates WHERE chocolate_id = 1000) a
JOIN
(
  (SELECT id, boy_id FROM boys_chocolates WHERE chocolate_id = 1003) b,
  (SELECT id, boy_id FROM boys_chocolates WHERE chocolate_id = 1006) c,
  (SELECT id, boy_id FROM boys_chocolates WHERE milk_id = 2000) d,
  (SELECT id, boy_id FROM boys_chocolates WHERE milk_id = 2001) e,
  (SELECT id, boy_id FROM boys_chocolates WHERE milk_id = 2005) f
)
ON a.boy_id = b.boy_id AND a.boy_id = c.boy_id AND a.boy_id = d.boy_id 
  AND a.boy_id = e.boy_id AND a.boy_id = f.boy_id AND b.id > a.id 
  AND c.id > b.id AND e.id > d.id AND f.id > e.id;

Replace 1000 1003 1006 with your first chocolate_id, second chocolate_id, third chocolate_id respectively. Also replace 2000 2001 2005 with your first milk_id, second milk_id, third milk_id.

MySQL Relational Division with multiple IDs

1 Answers1