Skip to content

Find a record containing all or a part of a list of id of other table in a many-to-many relation on MySql/Jpa?

In my web application, I have 3 tables:

  • TAB A: Recipes (id, name)
  • TAB B: Recipes_Ingredients(id, fk_recipe,fk_ingredient, quantity) Bridge table
  • TAB C: Ingredients (id, name)

I need to find all the recipes(TAB A) that containing a provided list of ingredients (TAB C).


I provide a list of ingredients id like 111 (spaghetti), 222(pepper), 333(oil) and I need to find all the recipes that containing these ingredients. The query should return me all those recipes that contain at most these ingredients.

So if I run a search like this:

WHERE TAB_B.fk_ingredients IN (111,222,333)  // IT DOESN'T WORKS AS I WISH

It must return a list of recipe that a list of recipes they contain MAX thath ingredients (111,222,333) and MINIMUM one of the ingredients I passed it.

Result Example:

id name list_fk_ingredients (es.)
1 Spaghetti with pepper 111,222
2 Spaghetti with oil 111,333
3 Pepper with nothing 222
4 Spaghetti with everything 111,222,333

I would also need a way to report the query back to JPA.

Thanks in advance!




Try 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,333) THEN 1 ELSE NULL END) =0;
User contributions licensed under: CC BY-SA
4 People found this is helpful