Skip to content
Advertisement

MySQL Filter on many-to-many on recipes – ingredients tables

In my application, I have 3 tables:

  1. recipes
  1. ingredients
  1. recipes_ingredients

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:

I also tried to change HAVING COUNT in

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

It must return:

RECIPE_ID
1
2
6

Advertisement

Answer

So maybe you can use this query

demo fiddle link

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement