February 15, 2010 at 3:08 am
hi folks,
I need ur help to finding least node from the tree.
consider the following snippet:
--drop table tabA
Create table tabA(P_Sequ numeric(5),description varchar(50),c_sequ numeric(5))
Go
insert into tabA values(1,'A',0)
go
insert into tabA values(2,'B',1)
go
insert into tabA values(3,'C',1)
go
insert into tabA values(4,'D',1)
go
insert into tabA values(5,'E',2)
go
insert into tabA values(6,'F',2)
go
insert into tabA values(7,'G',3)
go
insert into tabA values(8,'H',6)
go
insert into tabA values(9,'I',6)
GO
my exact requirement is the recursive query has to be return least nodes like H,I,G,D(i.e those nodes don't have any child nodes)
I did the following recursive but it return all the levels:
WITH CTE_temp(P_Sequ,description,c_sequ,Level)
AS
(
-- Root
Select t1.P_Sequ,t1.description,t1.c_sequ,0 As Level
from tabA t1
where c_sequ = 0
UNION ALL
-- child
Select t2.P_Sequ,t2.description,t2.c_sequ,Level+1 As Level
from tabA t2
INNER JOIN CTE_temp t3 on t2.c_sequ=t3.p_sequ
)
-- Statement that executes the CTE
Select * from CTE_temp
Kindly do the needful.
thnks in adv.
-Asan
February 15, 2010 at 3:51 am
What's wrong with the non-recursive
select P_Sequ, description from tabA
where P_Sequ not in (select c_sequ from tabA)
The if you need the levels you just join it to your exisitng CTE.
Derek
February 15, 2010 at 3:51 am
I forgot to mention I need root path as well..
For example:
Take 'H' since it is least node. I need output like this A-B-F-H
February 15, 2010 at 3:55 am
thanks Derek..
plz look in to above comments too..
February 15, 2010 at 4:21 am
WITH CTE_temp(P_Sequ,description,c_sequ,Level,nodepath)
AS
(
-- Root
Select t1.P_Sequ,t1.description,t1.c_sequ,0 As Level, '0.'+ CAST(t1.P_Sequ AS VARCHAR(MAX)) nodepath
from tabA t1
where c_sequ = 0
UNION ALL
-- child
Select t2.P_Sequ,t2.description,t2.c_sequ,Level+1 As Level,
nodepath + '.' + CAST(t2.P_Sequ AS VARCHAR(MAX)) nodepath
from tabA t2
INNER JOIN CTE_temp t3 on t2.c_sequ=t3.p_sequ
)
-- Statement that executes the CTE
Select * from CTE_temp
where p_sequ not in
(select c_sequ from tabA)
February 15, 2010 at 4:29 am
much of thanks Fazalul Haque n all..
nice effort..
got a solution..
February 15, 2010 at 4:43 am
Slight correction - remove '0.' from nodepath as it's meaningless and add nodepath2 in W-X-Y-Z format
;WITH CTE_temp(P_Sequ,description,c_sequ,Level,nodepath,nodepath2)
AS
(
-- Root
Select t1.P_Sequ,t1.description,t1.c_sequ,0 As Level, CAST(t1.P_Sequ AS VARCHAR(MAX)) nodepath
,convert(varchar(max),t1.description) as nodepath2
from tabA t1
where c_sequ = 0
UNION ALL
-- child
Select t2.P_Sequ,t2.description,t2.c_sequ,Level+1 As Level,
nodepath + '.' + CAST(t2.P_Sequ AS VARCHAR(MAX)) nodepath
,convert(varchar(max),nodepath2 + '-' + t2.description) nodepath2
from tabA t2
INNER JOIN CTE_temp t3 on t2.c_sequ=t3.p_sequ
)
-- Statement that executes the CTE
Select * from CTE_temp
where p_sequ not in
(select c_sequ from tabA)
Derek
February 15, 2010 at 6:36 am
Mohamed Asane (2/15/2010)
much of thanks Fazalul Haque n all..nice effort..
got a solution..
So... post your solution, please. Two way street here.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 24, 2010 at 2:35 pm
Hi Jeff, I have a stored procedure I want you to look at, its not an easy one which is why I am asking for your assistance. (Dont want to post on this forum since its taken from customer site).
Can email it to you, can you assist ?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply