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?

Question

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 1

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;

Answer 2

First, you need a subquery where you join the tables then group them by Recipes

SELECT R.name, count(R.id) AS sum
FROM Recipes as R
join Recipes_Ingredients as RI on R.id=RI.fk_recipe
group by R.id

It will output you a list of all the recipes with a count of how many ingredients each have. I named the count of ingredients as sum in here.

You don't need to join the ingredients table.

Next, you just select the records which have 3 or less and 1 or more from this query using a WHERE statement.

SELECT R.name, sum
FROM (SELECT R.name, count(R.id) AS sum
FROM Recipes as R
join Recipes_Ingredients as RI on R.id=RI.fk_recipe
group by R.id) as T
WHERE sum <= 3
AND sum >=1