>Hi Cetin,
>
>>In SQL2005 AdventureWorks database check stored procedures. If I don't remember wrong one the procedures was almost exactly this, creating a bike using CTE with recursion.
>>
>>PS: Looks like I remember right:)
>>uspGetBillOfMaterials
>>uspGetWhereUsedProductID
>
>Thx. I'll take a look at that. I actually found several examples on the web (the most useful one, as usual, *after* I struggled to a solution).
>
>I've still got one problem though- see my post to Sergey...
>Regards,
>Viv
Viv,
Probably I don't understand what you mean else this fits your sample:
WITH Parts
(id,recipeid,quan,itemid,itemisrecipe,portion,_level) AS
(
SELECT
id,recipeid,quan,itemid,
itemisrecipe,portion,0 as _level
FROM TestR
WHERE (recipeid = 23)
UNION ALL
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
Cetin