Hi,

>Probably I don't understand what you mean else this fits your sample:
>
>

>WITH Parts
>(id,recipeid,quan,itemid,itemisrecipe,portion,_level) AS
>(
>-- Anchor
>SELECT
>  id,recipeid,quan,itemid,
>  itemisrecipe,portion,0 as _level
>  FROM TestR
>  WHERE (recipeid = 23)
>UNION ALL
>-- Recursive
>SELECT
>  r.id,r.recipeid,r.quan,r.itemid,
>  r.itemisrecipe,r.portion,_level + 1
>FROM TestR r
>  INNER JOIN Parts p ON p.itemid = r.recipeid and p.itemisrecipe=1
>)
>select id,recipeid,quan,itemid,itemisrecipe,portion,_level from parts

Your query just returns the 'nesting level'. I need to accumulate the total weights for each part (or ingredient). The query (below) that I posted to Sergey does that where the 'quan' field value specifies a multiple (or fraction) of a sub-recipe - which is the case when the 'portion' field is true.
However if portion=false then the quan field contains the required *weight* of the sub-recipe. As an example from the sample data I posted: if portion=true for id 94 then it would mean that we require 100 x id231 whereas if portion were false it would indicate that we require 100gms of id231.

The problem is with the latter instance. To determine the needed amounts of id23 and id11 requires pre-knowledge of the total weight of ingredients for recipe 231 (i.e 130gms). Given this I can convert the quan value to a factor (in this instance it would be ~.769 (100/130) and use that in the recursion.

Maybe there is a way of re-formulating the query to achieve this - but, for the present, I'm stumped!

I wrote a recursive C# function which does the job and may stick with that - especially since there are other variations that need to be handled. One example of this is the 'expandthis' field. If false for a sub-recipe this indicates that this item should not be expanded in the final result set (though if portion=true I'd still need to recurse the content to obtain it's weight). Argghhhh,
Viv

WITH Parts AS (
-- Anchor
SELECT 
id,
recipeid,
quan,
itemid,
itemisrecipe
FROM TestR WHERE (recipeid = 23)

UNION ALL
-- Recursive
SELECT
r.id,
r.recipeid,
CAST(r.quan * p.quan AS numeric(7, 2)) AS quan,
r.itemid,
r.itemisrecipe
FROM TestR r INNER JOIN Parts p ON p.itemid = r.recipeid
WHERE (p.itemisrecipe = 1)
)

SELECT SUM(quan) quan, itemid,itemisrecipe
FROM Parts AS p2 WHERE itemisrecipe=0 GROUP BY itemid,itemisrecipe