mysql selection help needed

[joshman13]

i am working on a relational database of recipes.

 

i have a products table and a recipes table. i also have an ingredients table which is basically a link table between products and recipes so that basically a recipe will have many products through the ingredients table (i hope i am explaining that right).

 

what i am trying to do is select any number of products beforehand, and then write some sort of query that will find the recipe that matches those products exactly.

 

here is a simple schema of what i have now:

 

products:

  id

  name

 

recipes:

  id

  name

 

ingredients:

  id

  product_id

  recipe_id

 

so let's say i have a recipe called peanut butter and jelly. there 3 products in the products table: bread, peanut butter, and jam. so i then have 3 records in the ingredients table linking these 3 items to that recipe.

 

i need it so that if i have fewer ingredients than the recipe calls for, nothing will be returned. or if all of the correct ingredients are used, but also the id of the ketchup product is included, that will return nothing as well.

 

any ideas? thanks

[teng84]

i believe you're missing one table which is the used ingredients for each recipes so that we know if he missed or used unnecessary ingredients 

[joshman13]

isn't that what the ingredients table is? products is a list of all of the items that can be used in a recipe and ingredients is the table that links a product to a recipe as an ingredient.

 

the table names might be a little confusing.

[joshman13]

ok, i don't know if this is a good way of doing it but this sort of works:

 

i find all of the selected product ids and convert it to a comma separated string like "1,2,3"

 

and then run this:

 

SELECT recipe_id, GROUP_CONCAT(product_id) AS product_ids FROM ingredients GROUP BY recipe_id HAVING product_ids = '1,2,3' LIMIT 1

 

a potential problem might be that the order of the comma separated ids might be different but it's closer to what i need at least and maybe it can help give an idea of what i am trying to accomplish.

[fenway]

Easier way in simply to use an IN() clause with the product_ids, and then checking the count() = 3 with a having clause.

[joshman13]

ah, yes. i think i will try that.

 

initially i was using IN but since it returned records if any one of the ids matched, it was giving me problems.

[joshman13]

so this is what i came up with:

 

SELECT recipes.name, COUNT(*) AS ingredients_count
FROM ingredients JOIN recipes ON (ingredients.recipe_id = recipes.id)
WHERE ingredients.product_id IN (1,2,3)
GROUP BY recipe_id HAVING ingredients_count = 3 LIMIT 1

 

is that what you meant? it appears to work, but i'm still playing with it some more.

[fenway]

Yup, that's the one.

[joshman13]

after doing some testing, that didn't really work.

 

the recipes will not always have 3 ingredients so i would need a dynamic way of selecting the number of ingredients that each recipe has.

 

i changed it a little to this:

 

SELECT recipes.*, (SELECT COUNT(*) FROM ingredients WHERE ingredients.recipe_id = recipes.id) AS ingredients_count
FROM `recipes` JOIN ingredients ON (ingredients.recipe_id = recipes.id)
WHERE (ingredients.product_id IN (1,2,3))
GROUP BY recipe_id HAVING ingredients_count = 3 LIMIT 1

 

for the most part i am still pretty new to some of the more advanced features of mysql besides your standard select statements. my question is how would a query like this effect performance? is it ok to have a select within another select or should i try to avoid something like that?

[fenway]

I'm confused... you said you only wanted those with all three matching... how is this dynamic?