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)

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?

---