need help with Recusive CTE

Question

Post reply

need help with Recusive CTE

Adi Cohn-120898

SSC-Dedicated

Points: 33944
More actions
May 26, 2009 at 2:53 am

#97571

I need some help with recusive CTE. I have a table with 3 column – CategoryID, ParentID and NumOfProducts.
create table Hirarchy (CatID int, ParentID int, NumOfProducts tinyint)
go
insert into Hirarchy (CatID, ParentID, NumOfProducts)
select 1, null, 0
union
select 2, 1, 2
union
select 3, 2, 1
union
select 4, 1, 2
union
select 5, 1, 1
union
select 6, 5, 2
union
select 7, 1, 1
union
select 8, 6, 10
I need to find for each category how many products it has and add to it the number of products that its descendant categories have. For example Category 1 should show 19 because all the other categories are its descendants and all of them have total of 19 products. Category 5 should show 13 (10 products that belong to category 8 + 2 products that belong to category 6 + 1 product that belong directly to category 5). I managed to do it without recursive CTE, but I'm pretty sure that there is a way to do it with recursive CTE. Any help will be appreciated.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

Try this

declare @CatID int

--set @CatID=1;

set @CatID=5;

with cte as (

select CatID,NumOfProducts

from Hirarchy

where CatID=@CatID

union all

select h.CatID,h.NumOfProducts

from Hirarchy h

inner join cte c on c.CatID=h.ParentID

)

select sum(NumOfProducts) as NumOfProducts

from cte

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Thank you for the reply, but this is not what I wanted. I want to get the results for all the categories in the table and not get the results just for a specific category.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 3

with cte as (

select CatID as RootCatID,CatID,NumOfProducts

from Hirarchy

union all

select c.RootCatID,h.CatID,h.NumOfProducts

from Hirarchy h

inner join cte c on c.CatID=h.ParentID

)

select RootCatID,sum(NumOfProducts) as NumOfProducts