how to count number of left and right nodes of a root node in sql sever 2000

Thnk u Neel,

Hey Neel,
 your code really worked in my way ,i'l get back to u again if get any problem like this...
Once again thank you very very much, u cleared my way...

Hi Neel!

Thank u very much for ur help,

Your code really worked But..

i got the answer as leftcount=3 and rightcount as 3,i.e, its just counting the number of nodes in left and right position, but its my mistake that i didn't explained my question clearly ... 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) 

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

The previous will give the records where the root(1) or its chids are treated as Parent_node, Now this temp table having some duplicate records  because of the loop, the cursor will remove the dupliicate records and just get the count as result , u can club the two select statements  in to single  by the following SQL stmnt.
SELECT
(
COUNT(*)
) AS Child_Counts
FROM
#tempNodeFinal t
GROUP BY
t.Position
in result first one will be left childs and second will be Right childs

 
Instead of

"Select DISTINCT node ,parent_node ,Position ,node_name from #tempNodeFinal "

 
Replace with the following code
 
DECLARE @Count int
DECLARE @node_dup INT
DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT NODE, Count(*) - 1
FROM #tempNodeFinal
GROUP BY NODE
HAVING Count(*) > 1
OPEN dublicate_cursor
FETCH NEXT FROM dublicate_cursor INTO @node_dup, @Count
WHILE @@FETCH_STATUS = 0
BEGIN
SET ROWCOUNT @Count
DELETE FROM #tempNodeFinal WHERE NODE = @node_dup
SET ROWCOUNT 0
FETCH NEXT FROM dublicate_cursor INTO @node_dup, @Count
END
CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor
Select COUNT(*) as Leftcount from #tempNodeFinal where Position LIKE 'L'
Select COUNT(*) as Rightcount from #tempNodeFinal where Position LIKE 'R'
 
 
Others will remain the same

Hi Neel!

I tried your code but i'm just getting all the entries in the table as a result,if possible can you please elaborate your code with output specified...
I'l be very thankfull...

hi, PLease check the script
/* Orginal Table */
create Table #tempNode(node int,parent_node int,Position varchar(10),node_name varchar(10))
/* Static Data */
INSERT INTO #tempNode values(1,0,'L','abc')
INSERT INTO #tempNode values(2,1,'L','b1')
INSERT INTO #tempNode values(3,1,'R','b2')
INSERT INTO #tempNode values(4,2,'L','c1')
INSERT INTO #tempNode values(5,2,'R','c2')
INSERT INTO #tempNode values(6,3,'L','d1')
INSERT INTO #tempNode values(7,4,'R','e1')

/* Create 2 temp tables where one will be inserted for the final out put and other for
Temporary purpose */

create Table #tempNodeFinal(node int,parent_node int,Position varchar(10),node_name varchar(10))
create Table #tempNodeTemporary(node int,parent_node int,Position varchar(10),node_name varchar(10))
/* Static 'where' condition for Query better make it as SP and get as i/p par*/
INSERT INTO #tempNodeFinal(node ,parent_node ,Position ,node_name )
SELECT node,parent_node,Position,node_name FROM #tempNode where parent_node= 1
/* Insert the Root Node and its childs */
INSERT INTO #tempNodeTemporary(node ,parent_node ,Position ,node_name )
SELECT node,parent_node,Position,node_name FROM #tempNode where parent_node= 1

DECLARE @cnt INT
DECLARE @Loop INT
SET @Loop = 0
SELECT @cnt = COUNT(*) from #tempNodeTemporary
WHILE(@Loop< @cnt)
BEGIN
INSERT INTO #tempNodeFinal(node ,parent_node ,Position ,node_name )
SELECT a.node,a.parent_node,a.Position,a.node_name FROM #tempNode a INNER JOIN #tempNodeTemporary b on a.parent_node = b.node
INSERT INTO #tempNodeTemporary(node ,parent_node ,Position ,node_name )
SELECT a.node,a.parent_node,a.Position,a.node_name FROM #tempNode a INNER JOIN #tempNodeTemporary b on a.parent_node = b.node


SET @Loop= @Loop+1
IF(@Loop= @cnt)
BEGIN
/* Delete the Old parents*/

DELETE a from #tempNodeTemporary a JOIN #tempNodeTemporary b ON a.node= b.parent_node

/* Reset the count */
SELECT @cnt = COUNT(*) from #tempNodeTemporary

END


END
Select DISTINCT node ,parent_node ,Position ,node_name from #tempNodeFinal
--select * from #tempNodeTemporary
--drop table #tempNode
--drop table #tempNodeFinal
--drop table #tempNodeTemporary