SQL select all dishes matching ingredient criteria -

SQL select all dishes matching ingredient criteria -

im starting have headache figuring/searching query select dishes matches list of ingredients.

my tables:

ingredients ============ id ingredientname ingredientamount p_id dishes ========== id dishname dishingredients ========== id dishid ingredeientid ingredientamount

having i.e. 2 ingredient names, want select dishes ( want know p_id's) can create ( there many dishes subset of given i.e. 5 ingredients.)

up moment have following:

select d.name, i.ingredientname, di.ingredientamount dishes d inner bring together dishingredients di on d.dishid = di.dishid inner bring together ingredients on di.ingredientid = i.id i.ingredientname in ('salt','tomato')

although show dishes require 'tomato' , require other ingredients, dont have on list.

what create table dishingredients, columns dishingredientid, dishid, ingredientid , move ingredientamount table. because, instance, multiple dishes can have tomato's ingredient, different amounts.

then bring together these 3 tables find out dishes have tomato's ingredient this:

select d.name, i.ingredientname, di.ingredientamount dishes d inner bring together dishingredients di on d.dishid = di.dishid inner bring together ingredients on di.ingredientid = i.ingredientid i.ingredientname = 'tomato'

(and if want search more 1 ingredient, utilize in clause, did!)

i've re-read question, , maybe mean (any ingredient):

select distinct d.dishname dishes d inner bring together dishingredients di on d.dishid = di.dishid di.ingredientid in (select i.ingredientid ingredients i.ingredientname in ('tomato', 'cheese'))

it lists available dishes tomato , cheese ingredients. see sql fiddle

if want ingredients, seek this:

select d.dishname, a.ingredientname, b.ingredientname dishes d cross apply (select di.dishingredientid, i.ingredientname dishingredients di inner bring together ingredients on di.ingredientid = i.ingredientid , i.ingredientname = 'cheese' d.dishid = di.dishid) cross apply (select di.dishingredientid, i.ingredientname dishingredients di inner bring together ingredients on di.ingredientid = i.ingredientid , i.ingredientname = 'tomato' d.dishid = di.dishid) b

for each ingredient, add together cross apply.

sql select