Take the 2-minute tour ×

I have a database that has a table of Ingredients I and a table of Recipes R. The two tables have a many-to-many relationship, as one recipes uses many ingredients and one ingredient is used in many recipes. I have a third cross-reference table that uses the cross-reference validation pattern to enforce my many-to-many relationship, and is done using string foreign keys (instead of integers).

Assuming I have a collection of ingredients C outside of my database, how can I query Recipe table R for every recipe that can be made using ONLY the list of ingredients supplied in C?

Other things to consider

1) Speed will (of course) be a concern eventually, but correctness is what I'm stuck on at the moment.

2) The collection of ingredients C might be very large (~100 ingredients).

Any answers or even just pointers in the right direction would be greatly appreciated.



share|improve this question
What is the DB/Version? –  Chandu Jul 25 '12 at 21:15
@Chandu, I haven't figured out any of that yet, I'm just working on this system as a hobby so I haven't laid out any specifications yet. For now, you can just assume the current stable MySQL 5.5.25 . But really, any answer you can provide I will try and port back to MySQL (or whatever DB I decide to use) –  pghprogrammer4 Jul 25 '12 at 21:51
add comment

1 Answer

up vote 5 down vote accepted

One way is to write:

select ...
  from R
 where ID not in
        ( select R_ID
            from RI
           where I_ID not in
                  ( select I_ID
                      from C

That is: start with C. Select all recipe–ingredient cross-references where the ingredient is not in C. This gives you the set of all recipes that cannot be made using only ingredients in C. Then, select all recipes that aren't in that set.

share|improve this answer
Good catch on my answer. I read the question to fast. I missed the point about only recipes that can be made with ONLY those ingredients. –  RThomas Jul 25 '12 at 21:27
add comment

Your Answer


