Home > database >  MySQL Filter on many-to-many on recipes - ingredients tables
MySQL Filter on many-to-many on recipes - ingredients tables

Time:05-11

In my application, I have 3 tables:

  1. recipes
-id
-name
-description
  1. ingredients
-id
-name
  1. recipes_ingredients
-recipes_id
-ingredients_id

I need to find all those recipes that contain a list of ingredients_id and that I have in my food storage and containing NO OTHERS ids outside the list I provided (so not in my food storage).

Example: i provide oil (id=111) bread=(id=222) result recipes:

  • OK oil bread
  • OK bread
  • OK oil
  • NO oil salt
  • NO bread oil salt

this query IT DOESN'T WORKS because it gives me back the recipes that have exclusively and exactly the ingredients provided:

SELECT ri.recipes_id
FROM recipes_ingredients ri
WHERE ri.ingredients_id IN (111,222)
GROUP BY ri.recipes_id
HAVING COUNT(*) = 2;

I also tried to change HAVING COUNT in

HAVING COUNT(*) <= length_of_list_I_provided

but he gives me back all the recipes that, yes, have even only a part of the ingredients supplied but also the ingredients that I have not provided it in the list.

Do you have any ideas?

thanks and regards

UPDATE:

as they suggested to me, I show you an example of tables:

Recipes table:

ID NAME
1 Recipe 1
2 Recipe 2
3 Recipe 3
4 Recipe 4
5 Recipe 5
6 Recipe 6

Ingredients table:

ID NAME
111 Oil
222 Bread
333 Salt
444 Pepper

Recipes_Ingredients table:

RECIPES_ID INGREDIENTS_ID
1 111
1 222
2 222
3 222
3 333
4 111
4 222
4 333
5 333
5 444
6 111

In the query I give it my ingredients

IN(111,222)

It must return:

RECIPE_ID
1
2
6

CodePudding user response:

So maybe you can use this query

SELECT ri.recipes_id
FROM recipes_ingredients ri
GROUP BY ri.recipes_id
HAVING COUNT(CASE WHEN ri.ingredients_id NOT IN (111,222) THEN 1 ELSE NULL END) =0;

demo fiddle link

  • Related