Need T- SQL help on binary tree structure table

Question

Post reply

Need T- SQL help on binary tree structure table

virgo

Ten Centuries

Points: 1266
More actions
April 21, 2009 at 5:45 am

#133193

Hi Guys,
here is a table sturucture for managing a chain link(binary tree)application.
user_registration_tbl
(id int identity,
user_first_name varchar(100) not null,
parentid int not null, ---coresponding ID of parent
Node int not null, ----'0' if member is leftside of parent and '1' if right.
creationtime datetime null,
primary key(id));
Id U_f_name parentid Node creationtime
1john00NULL
2jack10NULL
3jam11NULL
4sam20NULL
5sat21NULL
6 jay 3 0
7 rai 3 1
8 ram 4 0
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 2 the procedure should give its
total number of left nodes =2
total number of riht nodes = 1
Thanks in advance

Viewing 6 posts - 1 through 6 (of 6 total)

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

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

declare @id int

set @id=2;

with cte(id,lft,rgt) as (

select id,0,0

from user_registration_tbl

where id=@id

union all

select t.id,1-t.Node, t.Node

from user_registration_tbl t

inner join cte c on c.id = t.parentid)

select sum(lft) as lft,

sum(rgt) as rgt

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

virgo Ten Centuries Points: 1266 More actions · Answer 2

thanks brother it works.

But can you please let me know how can i do the same with out using CTE feature of sql server , like may be using procedure or function, as here i need to implement this logic in mysql where CTE is available...

thanks a heap!!!

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

virgo (4/21/2009)
thanks brother it works.
But can you please let me know how can i do the same with out using CTE feature of sql server , like may be using procedure or function, as here i need to implement this logic in mysql where CTE is available...
thanks a heap!!!

If you are using SQL Server 2000, which doesn't have recursive CTEs, you could use recursive functions or recursive stored procedures. There's plenty of examples on the internet if you search for them. As for mysql, I've never used it, so I can't comment.

If you can change the schema, you can model your hierarchy using "nested sets", this doesn't require recursion and can be very fast for aggregate queries (but can be slow for changes to the hierarchy).

____________________________________________________

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

virgo Ten Centuries Points: 1266 More actions · Answer 4

thanks for the info...can you please help me out with a piece of recursive function for this scenairo

Really appreciate you help on this 🙂

Vaghasiya Nilesh SSChasing Mays Points: 633 More actions · Answer 5

sorry bro..

its not properly work..

u can add some more data and check it..