Need T- SQL help on binary tree structure table

Question

Post reply

Need T- SQL help on binary tree structure table

Viewing 5 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: 26986 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: 26986 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..