Recursive Query - 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 2005 Forums

Transact-SQL (2005)

Recursive Query

Author

Topic

asderex
Starting Member

10 Posts

Posted - 2009-04-26 : 18:42:23

Hi,

This reminds me of something they would have asked me in a university exam but I can't figure it out.

I have a table that essentially holds a list of ingredients i.e. recipes. Some of those ingredients may be recipes themselves. Note: root recipes are expressed to make 1 Kg of the product. I am trying to write a query that finds a total of the root (i.e. very base components) products (and quantities).

For example my table might look like:

Recipe Name----->Ingredient----->Quantity
Bread -----> Water -----> 100G (A)
Bread -----> Egg -----> 200G (B)
Bread -----> Baking Flour -----> 200G (C)
Baking Flour -----> Normal Flour -----> 800G (D)
Baking Flour -----> Yeast -----> 150G (E)
Baking Flour -----> Salt -----> 50G (F)
Yeast -----> Farmer Yeast -----> 950G (G)
Yeast -----> Chemical Yeat -----> 50G (H)

So if I wanted to query the ingredients of Bread I would like to produce the following:

Recipe----->Ingredient----->Quantity
Bread----->Water----->100G
Bread----->Egg----->200G
Bread----->Normal Flour----->160G (800 * .2) (C * D)
Bread----->Salt----->10G (50G * .2) (C * F)
Bread----->Farmers Yeast----->28.5G (950 * .15 * .2) (C * E * G)
Bread----->Chemical Yeast----->1.5G (.05 * .15 * .2) (C * E * H)

Theoretically this list can be infinitelly deep. i.e. a recipe my go down 20 or more layers (but probably not more than five or six). I was wondering if there was a tidy recursive SQL query I could write that could handle any situation.

Any help would be greatly appreciated.

Thanks

Asderex

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-26 : 20:24:59

You have bad table design. Look into normalization. The recipe and ingredients can be normalized.

[Recipe]
Id
Name
IsIngredient

[Ingredient]
Id
Name

[RecipeDirections]
RecipeId
IngredientId
Quantity
BaseFactor

asderex
Starting Member

10 Posts

Posted - 2009-04-27 : 00:14:47

Hi Whitefang,

Thanks for the reply. I agree - it is not good table design but unfortunatley I didn't design the DB (and can not change it). I was just wondering if there was a query that could handle this situation or is it just a case of poor design and no solution?

Thanks

Asderex

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-27 : 08:06:32

[code]
CREATE TABLE #Recipe (
RecipeName VARCHAR(50),
Ingredient VARCHAR(50),
Quantity INT
)

INSERT INTO #Recipe (RecipeName, Ingredient, Quantity)
VALUES ('Bread', 'Water', 100);
INSERT INTO #Recipe (RecipeName, Ingredient, Quantity)
VALUES ('Bread', 'Egg', 200);
INSERT INTO #Recipe (RecipeName, Ingredient, Quantity)
VALUES ('Bread', 'Baking Flour', 200);
INSERT INTO #Recipe (RecipeName, Ingredient, Quantity)
VALUES ('Baking Flour', 'Normal Flour', 800);
INSERT INTO #Recipe (RecipeName, Ingredient, Quantity)
VALUES ('Baking Flour', 'Yeast', 150);
INSERT INTO #Recipe (RecipeName, Ingredient, Quantity)
VALUES ('Baking Flour', 'Salt', 50);
INSERT INTO #Recipe (RecipeName, Ingredient, Quantity)
VALUES ('Yeast', 'Farmer Yeast', 950);
INSERT INTO #Recipe (RecipeName, Ingredient, Quantity)
VALUES ('Yeast', 'Chemical Yeast', 50);

WITH RecursiveRecipe (RecipeName, Ingredient, Quantity, ItemMultiplier, NestedMultiplier) AS
(
SELECT RecipeName, Ingredient, Quantity, CAST(1 AS dec(5,2)), CAST(1 AS dec(5,2))
FROM #Recipe WHERE RecipeName = 'Bread'

UNION ALL

SELECT recipe.RecipeName, recipe.Ingredient, recipe.Quantity,
CAST(CAST(rec.Quantity AS dec(5,2))/1000 as dec(5,2)),
CAST(REPLACE(ItemMultiplier,0.00,1.00) as dec(5,2))
FROM #Recipe AS recipe, RecursiveRecipe AS rec WHERE recipe.RecipeName = rec.Ingredient
)
SELECT RecipeName, Ingredient, CAST(Quantity*ItemMultiplier*NestedMultiplier AS dec(5,2)) AS Quantity
FROM RecursiveRecipe WHERE RecursiveRecipe.Ingredient NOT IN (SELECT RecipeName FROM #Recipe)
[/code]

asderex
Starting Member

10 Posts

Posted - 2009-04-27 : 21:07:30

Awesome Whitefang... thanks for your help.

suencien
Starting Member

3 Posts

Posted - 2012-05-16 : 10:55:24

Hi Whitefang,

I know your post was a long time ago. But could you please help me if the table structure is

[Recipe]
RecipeID
RecipeName

[Ingredient]
IngredientID
IngredientName

[IngredientInRecipe]
RecipeID
IngredientID
Quantity
IsRecipe

Thank you for your help Whitefang :)

Andry

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-16 : 11:03:56

Whitefang isn't likely to answer, as he hasn't logged in in over 3 years. We also prefer to start new threads rather than post to 3-year old ones.

What specifically do you need help on? Are you doing recursive recipe designs?

suencien
Starting Member

3 Posts

Posted - 2012-06-02 : 00:42:07

quote:
Originally posted by robvolk

Whitefang isn't likely to answer, as he hasn't logged in in over 3 years. We also prefer to start new threads rather than post to 3-year old ones.

What specifically do you need help on? Are you doing recursive recipe designs?

Yes... Thank you for responsing :) I was trying whitefang's approach but it only works with recursive up to 3 levels.

This is my current simplified database design

[RECIPE]
RecipeID (PK)
RecipeName
QuantityMade

[INGREDIENT]
IngredientID (PK)
IngredientName

[RECIPEINGREDIENT]
RecipeID (PK)
IngredientID (PK)
IsRecipe
Quantity

I need to build a report of all ingredients and quantity used in a recipe. There could be a recipe used in another recipe, and it can go down up to 10 levels.

Could you please help me with this :)

Andry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-02 : 01:49:57

how is recursive relationship setup? I cant see any field which enables you to link recursively here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

suencien
Starting Member

3 Posts

Posted - 2012-06-02 : 02:04:45

Hi :)

Sorry, a bit of change

[RECIPE]
RecipeID (PK)
RecipeName
QuantityMade

[INGREDIENT]
IngredientID (PK)
IngredientName

[RECIPEINGREDIENT]
RecipeID (PK)
IngredientID (PK)
IsRecipe (PK)
Quantity

NOTE: IngredientID stores either IngredientID or RecipeID, if RecipeID, IsRecipe is true

The database structure might not be correct and you are welcome to change.

Thank you :)

Andry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-02 : 13:41:19

doesnt look like a good db design. so how does data recurse? can you show some sample data to illustrate parent child relationship?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources