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