In my application, I have 3 tables:

1. recipes

-id
-name
-description

2. ingredients

-id
-name

3. 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