mysql - Retrieving records from pivot table which match all results -


i making place can store recipes have made having trouble database. have 3 tables, recipes, ingredients , baseingredients table.

recipe table id int name varchar steps varchar  baseingredients table id int name varchar  ingredients table id int baseid int recipeid int measurement varchar 

so everytime enter in recipe asked ingredients in it, , checks see if exists in base ingredients table, if uses baseingredient id , fills in record in ingredients table.

my issue is, on searching recipes, want able search on ingredients have in fridge. if type in eggs , bread, come egg sandwich. query im using, find recipes have eggs in , return back, example find cakes, pancakes (whatever contains eggs really). how can limit shows recipes contain ingredients have listed search parameter.

select recipes.id, recipes.name recipes inner join ingredients on recipes.id = ingredients.recipeid inner join baseingredient on ingredients.baseid = baseingredient.id  baseingredient.name = 'eggs'  

select r.id       ,r.name recipes r exists (select *                ingredients i1               inner join baseingredients b1                 on i1.id=b1.baseid               i1.recipeid = r.id                , b1.name = 'ingredient_1'              )   ...   , exists (select *                ingredients in               inner join baseingredients bn                 on in.id=bn.baseid               in.recipeid = r.id                , bn.name = 'ingredient_n'              ) 

edit: above query return recipes ingredient lists include specified. if misunderstood question , want recipes use no other ingredients specify, try this:

select r.id       ,r.name recipes r not exists (select *                    ingredients                   inner join baseingredients b                     on i.id=b.baseid                   i.recipeid = r.id                   , b.name not in ('ingredient_1', 'ingredient_2', ..., 'ingredient_n') 

where ingredient_1, ingredient_2, etc should replaced enter in search.


Comments

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

vue.js - Create hooks for automated testing -

Add new key value to json node in java -