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

Es:

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!

Regards

Answer

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;