Query Help Needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help Needed

Author

Topic

glant
Starting Member

2 Posts

Posted - 2011-01-15 : 00:25:48

Hi,

I have a recipe table and ingredients table which has a one-to-many relationship where each recipe has multiple ingredients. I am trying to create a query that will return each recipe that has the specified ingredients. The specified ingredients is a dynamic list where the user can continually add or remove items to be include.

For example:


RECIPE
id   NAME
1    Recipe1
2    Recipe2
3    Recipe3

RECIPE_INGREDIENTS
id    fk_reicpe_id   fk_ingredient_id
1         1                1
2         1                2
3         2                1
4         2                3
5         3                1
6         3                2
7         3                4

INGREDIENTS
id    ingredient
1       milk
2       egg(s)
3       water
4       sugar

so if the user enters milk recipe 1,2,3 will be returned. If the user adds egg(s) in addition to milk the result would be 1,3. If the user adds sugar in addition to milk and eggs recipe 3 would be returned.

Any help would be greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-15 : 01:04:19

This is a classic example of Relational Division.
Have a look at these two blog posts

http://weblogs.sqlteam.com/peterl/archive/2010/06/30/Relational-division.aspx
http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx

N 56°04'39.26"
E 12°55'05.63"

glant
Starting Member

2 Posts

Posted - 2011-01-15 : 23:27:47

Thank you very much for your reply. Those blogs were very helpful!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources