select in many to many relationship

Question

If I have a database with which can be seen as a recipe database, how can I select all the id's of the recipes I can make with given ingredients, for example if I have ingredient 1 and 4 it will return 3 as that is the only recipe I can make with that.

There will be approximately 50 ingredients and many more recipes.

I see several possibilities like using UNION/several joins, ... but I don't see a straightforward simple solution to this problem.

Recipes   Recipes/Ingredients    Ingredients
   1            1  1                  1
   2            1  2                  2
   3            2  1                  3
                2  3                  4
                2  4
                3  1
                3  4

edit/

What I thought of to solve it:

select recipe_id from Recipes
where recipe_id in ( 
    select recipe_id from Recipes_Ingredients where ingredient_Id = 1
    UNION
    select recipe_id from Recipes_Ingredients where ingredient_Id = 4
)

This will lead to very long queries as my database isn't really about ingredients but about stuff that can have 50 or more of these things in them.

check this link http://stackoverflow.com/questions/349559/sql-how-to-search-a-many-to-many-relationship — Mingebag, Mar 13 '13 at 14:21
@Aarolama Bluenk I have searched and I will be able to solve the question with UNION statements. I think this is not the best solution and up to this moment I haven't found better ways, which is why I think people with daily experience with this will be able to give a straightforward answer. I don't expect a query or something, just the appropriate way to tackle these kind of queries. — J. Maes, Mar 13 '13 at 14:22
@J.Maes In my opinion, you need to present what you've tried. If you don't then the best that we can do is point you towards how to do it. We don't write code. — Kermit, Mar 13 '13 at 14:25
@PM 77-1 I have considered joining, but I think I would be left with a table containing multiple of the same ideas and their ingredients. I need the recipes with all the ingredients. — J. Maes, Mar 13 '13 at 14:44
@Aarolama Bluenk In my post you'll find what I thought of. I don't expect code, just something like you can use THIS to filter out those and then you'll be able to get the right ones with THAT. — J. Maes, Mar 13 '13 at 14:46

vaugham · Accepted Answer · 2013-03-13T14:48:19.067

Try this (I tried to guess your columns names) :

Table Recipes : Recipe_id

Table Recipes_Ingredients : Recipes | Ingredients

  SELECT Recipe_id FROM Recipes   
  EXCEPT (
    SELECT DISTINCT Recipes FROM Recipes_Ingredients
    WHERE Ingredients NOT IN (1,4)  
  )

I will explain this. If you want to know the "recipes you possess at least one ingredient" (here : 1,4) :

SELECT DISTINCT Recipes FROM Recipes_Ingredients
WHERE Ingredients IN (1,4)

So the contrary is "which recipe i can't do" because, you miss at least one ingredient :

SELECT DISTINCT Recipes FROM Recipes_Ingredients
WHERE Ingredients NOT IN (1,4)

Then we take all the recipes with the exception of the ones you can't do :

SELECT recipe_id FROM Recipes   
  EXCEPT (
    SELECT DISTINCT Recipes FROM Recipes_Ingredients
    WHERE Ingredients NOT IN (1,4)  
  )

This looks very promising, looks exactly what I'm looking for. I'm testing it now :) — J. Maes, Mar 13 '13 at 14:46

score 1 · Answer 2 · 2013-03-13T14:44:17.640

1

Try:

select recipe_id
from recipes_ingredients
group by recipe_id
having count(*)=sum(case when ingredient_id in (1,4) then 1 end)

edited Mar 13 '13 at 14:44

answered Mar 13 '13 at 14:39

select in many to many relationship

2 Answers2