>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
>

Viv,
You're trying but I'm having a hard time to understand - in pseudocode and expected results, or a diagram what comes from where sample might understand but who has time to create that:) I based it on a sample I saw saying "Component/Ingredient..."
Anyway if you've a working C# version then why not add it as an SP to SQL server:)
Cetin