Search This Blog & Web

Tuesday, December 15, 2009

Using multiple CTE 's in single SQL Statement

Declare @table table (id int,name varchar(50),parentid int) Insert into @table values (1,'Director',NULL)
Insert into @table values (4,'City Manager Fsd',3)
Insert into @table values (5,'Area Manager Fsd',4)
Insert into @table values (2,'County Manager',1)
Insert into @table values (3,'Country Manager 2',1)
Insert into @table values (6,'City Manager ISB',2)
Insert into @table values (7,'Co-director',NULL)
Insert into @table values (8,'Country Head',7)
Insert into @table values (9,'Sales Manager',7)
Insert into @table values (9,'Sales Manager',4);

with CTE(id ,name,parentid,levels) as
(Select id,name,parentid ,1 as levels from @table t where parentid =1
union all
Select td.id,td.name,td.parentid,levels+ 1 as levels from @table td inner join cte on td.parentid = cte.id
),

CTE1(id ,name,parentid,levels) as
(
Select id,name,parentid ,1 as levels from @table t where parentid =7
union all
Select td.id,td.name,td.parentid,levels+ 1 as levels from @table td inner join cte on td.parentid = cte.id
)

select CTE.* from CTE inner join CTE1 on CTE.id = CTE1.id
 
cheers:)

No comments: