SQL query for recipes that can be made from a collection of ingredients

Question

I have a database in which I have a table of ingredients and a recipe table R. Two tables have a many-to-many relationship, as many recipes are used in one recipe, and one ingredient is used in many recipes. I have a third cross-reference table that uses a cross-reference validation pattern to enforce many-to-many relationships and runs using a string of foreign keys (instead of integers).

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

Other things to consider

1) Speed ​​will (of course) end up being a concern, but the correct thing is that I'm stuck at the moment.

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

Any answers or even pointers in the right direction are welcome.

Thanks,

Alec

Answer 1

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 cross-reference ingredients where the ingredient is not in C This gives you a set of all recipes that cannot be made using only the ingredients in C Then select all recipes that are not in this set.