syed afroz

syed afroz

  • NA
  • 54
  • 0

Counting number of left and right nodes without Using cursors.

Sep 5 2010 12:27 AM
Hi!
I'm using sql server 2000, I need to count number of nodes in left and right side of a root node....
suppose i've entries in table as

node   parent_node  Position  node_name
1           0                      'L'            abc
2           1                       'L'            b1
3           1                       'R'             b2
4           2                       'L'             c1
5           2                       'R'             c2
6           3                       'L'              d1

if i supplied node '1' then it should Give result as
left count=3      and
right count =2


actually what i want is this..

For my entries tree will be like this..
                                                               1(abc)
                                                                 /  \
                                                                /     \
                                                      (b1)  2       3(b2)    
                                                             /\            / \
                                                           /    \         /    \
                                                    (c1)4   5(c2)  6(d1)

Now if i give i/p as 1 then
eft count=3   (b1,c1,c2)   and
right count =2 (b2,d1)

if the tree is like this...
                                                    
            1(abc)
                                                                 /   |  \
                                                                /    |    \
                                                      (b1)  2     |    3(b2)    
                                                             /\      |      /
                                                           /    \    |    / 
                                                    (c1)4  5(c2)|  6(d1)
                                                                      |      /  \   
                                                                      |     /     \
                                                                      | (d2)7   8(e1)

the middle line is just imaginary to differentiate left and right nodes for node '1(abc)'

Now if i give i/p as 1 then
eft count=3   (b1,c1,c2)   and
right count =4(b2,d1,d2,e1) 


I DONT WANT TO USE CURSORS BECAUSE I'VE SOLUTION WITH CURSOR BUT THERE ARE THOUSANDS OF RECORDS IN THE TABLE ,SO EXECUTION TIME OF PROCEDURE IS V VERY HIGH SOMETIMES IT STRUCK SO PLEASE SUGGEST ME SOMETHING......

Please....Please help me out i'm strucked from many days...Thanx in advance..

Answers (2)