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

Question

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 Plz help me out.... Thanx in advance..

Neel Sreeraj · Answer

/* 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  3  'R'  'd2' ) INSERT INTO #tempNode values ( 8  4  'R'  'e1' ) INSERT INTO #tempNode values ( 9  4  'L'  'e2' ) /* 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 ) parent_Position 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  parent_Position ) SELECT node  parent_node  Position  node_name  CASE Position WHEN 'L' THEN 'LF' WHEN 'R' THEN 'RG' END 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 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 DECLARE @Count_new int DECLARE @cr_nodes INT DECLARE @cr_parent_node INT DECLARE @cr_Position VARCHAR ( 10 ) DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR SELECT NODE  parent_node  parent_Position FROM #tempNodeFinal OPEN dublicate_cursor FETCH NEXT FROM dublicate_cursor INTO @cr_nodes  @cr_parent_node  @cr_Position WHILE @@FETCH_STATUS - 1 BEGIN UPDATE a SET a . parent_Position = b . parent_Position FROM #tempNodeFinal a INNER JOIN #tempNodeFinal b on a . parent_node = b . node SET @Count_new = @Count_new + 1 FETCH NEXT FROM dublicate_cursor INTO @cr_nodes  @cr_parent_node  @cr_Position END CLOSE dublicate_cursor DEALLOCATE dublicate_cursor --UPDATE a SET a.parent_Position= b.parent_Position FROM #tempNodeFinal a INNER JOIN #tempNodeFinal b on a.parent_node= b.node Select COUNT (*) as Leftcount from #tempNodeFinal where parent_Position LIKE 'LF' Select COUNT (*) as Rightcount from #tempNodeFinal where parent_Position LIKE 'RG' --Select DISTINCT node parent_node Position node_nameparent_Position from #tempNodeFinal --select * from #tempNodeTemporary --drop table #tempNode --drop table #tempNodeFinal --drop table #tempNodeTemporary please use this query here again i am using one more cursor which is not a proper way  since i am bit busy i went by this approach will optimize this query and update you later.

Neel Sreeraj · Answer

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

Neel Sreeraj · Answer

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

syed afroz · Answer

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...

syed afroz · Answer

Hi Neel! T hank u very much for ur help Your code really worked But.. i got the answer as leftcount=3 and rightcount as 3i.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 (b1c1c2) and right count =2 (b2d1) 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 (b1c1c2) and right count =4(b2d1d2e1) Please....Please help me out i'm strucked from many days...Thanx in advance..

syed afroz · Answer

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

Neel Sreeraj · Answer

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