July 16, 2010 at 11:36 pm
Hi Guys,
here is a table sturucture for managing a chain link(binary tree)application.
tbl_tree
(node_id int identity,
parentid int not null, ---coresponding ID of parent
Node int not null, ----'0' if member is leftside of parent and '1' if right.
);
Id parentid Node
100
210
311
420
521
630
731
840
950
1051
1180
1281
1391
14100
1561
1670
1771
18110
19111
20131
So can any one please let me know a stored procedure logic for getting a total left count and right count for a particaulr Id.
e.g. if i pass Id 1 the procedure should give its
total number of left nodes =10
total number of riht nodes = 9
or
if i pass Id 3 the procedure should give its
total number of left nodes =4
total number of riht nodes = 4
in sql server 2005
Thanks in advance
July 17, 2010 at 12:06 am
i got perfect one solution ..
July 20, 2010 at 4:52 am
Hi,
Are you sure your samples are correct
I can not see that numbers of items on the left or right of node 3 or node 1
July 20, 2010 at 5:12 am
Here is my alternative solution according to my understanding
select * from tbl_tree
declare @nodeid int = 3
;with cte as (
select
node_id, parentid, node,
null lnode,
null rnode
from tbl_tree where node_id = @nodeid
union all
select
t.node_id, t.parentid, t.node,
ISNULL(cte.lnode, CASE WHEN t.node = 0 THEN 1 ELSE 0 END) lnode,
ISNULL(cte.rnode, CASE WHEN t.node = 1 THEN 1 ELSE 0 END) rnode
from tbl_tree t
inner join cte
on cte.node_id = t.parentid
)
select
@nodeid nodeid,
SUM(lnode) LeftNodes,
SUM(rnode) RightNodes
from cte
In the above script, I used T-SQL Recursive CTE (Common Table Expressions)
You can find an other sql code to create a numbers table using recursive cte expression
I hope that helps,
Eralper
July 28, 2010 at 4:19 am
thank u
July 31, 2010 at 5:01 am
Hi
I want count of binary tree levels..
example:
a ------------------------------>level-1
b c-------------------->level-2
d e f g ---------->level-3
thanks..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply