How do I return only rows where all conditions are met based on an array of intergers (IDs)?

Question

From a page - MyBarCabinet - I can select what raw materials I have available. These raw materials (only the ID's) is put into an array and stored in a cookie.

Now; my goal is to only get the drinks that do not require any more/other raw materials than what is stored in the cookie. In other words; If I've checked that I only have "16", "34" and "35" available: only the drinks that only require these raw materials should show up, and not those drinks that consists of these in addition to other raw materials..

I query drinks, and all that, from a MySQL database. This part is build up of three tables: drinks_recipes, drink_recipes_ingredients and raw_materials.
The drink_recipes_ingredients-table is used to "link" the drink recipes with the raw materials.

If I do:

SELECT 
    dr.id drink_id, dr.name drink_name,
    rm.name ingredient_name
FROM drink_recipes_ingredients dri
    JOIN drink_recipes dr ON dr.id = dri.fk_recipes_id
    JOIN raw_materials rm ON rm.id = dri.fk_raw_materials_id
WHERE rm.id IN (16,34,35)

This does exactly what I don't want. I'm getting all drinks that contains at least one of the selected raw materials.

I did have a look at this post: How to return a row only if multiple clauses are met, but I couldn't figure out how to do something similar in my case. But the goal looks like to be the same.

score 2 · Accepted Answer · answered Jan 02 '14 at 17:35

2

Baramar seems to have an interesting solution, but for this kind of problem I usually use the "find the opposite" method. We start by finding all the drinks that DON'T match your list. Then we select drinks that aren't those:

SELECT *
FROM drink_recipes
WHERE drink_recipes.id NOT IN (    
    SELECT 
        dr.id 
    FROM drink_recipes_ingredients dri
        JOIN drink_recipes dr ON dr.id = dri.fk_recipes_id
        JOIN raw_materials rm ON rm.id = dri.fk_raw_materials_id
    WHERE rm.id NOT IN (16,34,35)
)

answered Jan 02 '14 at 17:35

Digital Chris

6,177
1
20
29

1

Both this, and the suggestion by Barmar, worked the way I wanted. I've testet back and forth for a while now, and they both returns the expected result. I don't know if one of them is more perferred over the other, but the this code looked more neat. For now I'm going with this code.. – ThomasK Jan 02 '14 at 20:19

Barmar · Answer 2 · 2014-01-02T18:11:41.407

1

Instead of returning the rows that match the ingredients, count the number of selected ingredients matched. Then you can compare this to the total number of ingredients, to see if there are others.

SELECT 
    dr.id drink_id, dr.name drink_name,
    rm.name ingredient_name,
    SUM(rm.id IN (16, 34, 35)) AS matched_count,
    COUNT(*) AS all_count
FROM drink_recipes_ingredients dri
    JOIN drink_recipes dr ON dr.id = dri.fk_recipes_id
    JOIN raw_materials rm ON rm.id = dri.fk_raw_materials_id
GROUP BY dr.id
HAVING matched_count > 0 AND matched_count = all_count

edited Jan 02 '14 at 18:11

answered Jan 02 '14 at 17:26

Barmar

741,623
53
500
612

Why does your solution work? It is possible that some other recipe has three ingredients that are differs from {16, 34, 35}. But condition ` matched_count > 0 AND matched_count = found_count` will return `true`. – Nicolai Jan 02 '14 at 18:08
This is comparing counts for the same recipe, because of `GROUP BY`. – Barmar Jan 02 '14 at 18:14
Yes, I see now, thank you. I missed that SUM will sum only THAT ingredients we need. – Nicolai Jan 02 '14 at 18:18
This code looks more complex than the solution Digital Chris provided. Both of them worked the way I wanted. Is there any reason why the math-part in this code would be more accurate in some situations? – ThomasK Jan 02 '14 at 20:31
I wrote it this way because I first read your question as wanting recipes that used _all and only_ those ingredients. – Barmar Jan 02 '14 at 20:36
That is correct.. But if I checked 12 or 20 ingredients - because that's what I have available - I want all recipes that I can make with those ingrediens. And it seems like that works aswell... – ThomasK Jan 02 '14 at 21:03
Both queries will work. I'm just explaining why I wrote it the way I did. I started with that misunderstanding, wrote a query that solved it, then I made small changes to match the correct understanding. – Barmar Jan 02 '14 at 21:04

score 0 · Answer 3 · answered Jan 02 '14 at 18:04

I have other solution... maybe not so elegant from a mathematical point of view.

SELECT 
      dr.id drink_id
    , dr.name drink_name
    , rm.name ingredient_name
FROM 
    drink_recipes_ingredients dri
    JOIN drink_recipes dr ON dr.id = dri.fk_recipes_id
    JOIN raw_materials rm ON rm.id = dri.fk_raw_materials_id
GROUP BY dr.id, dr.name, rm.name
WHERE GROUP_CONCAT(rm.id ORDER BY rm.id SEPARATOR '|') = '16|34|35'

The string '16|34|35' is concatenated IDs from cookie. You can prepare it in application code.

How do I return only rows where all conditions are met based on an array of intergers (IDs)?

3 Answers3

Linked