Click here to Skip to main content
11,496,146 members (796 online)
The site is currently in read-only mode for maintenance. Posting of new items will be available again shortly.
See more: ASP.NET PHP C#4.0 databse
Given below is my code in ms sql2008 here my empty node contain the value null ,,,
please help me out from this ...or give any other program for counting the node in mssql 2008 for binary tree...


ALTER FUNCTION [vainimarketing].[Countnode](@node AS varchar(50))
Returns INT
AS
BEGIN
Declare @lnode Varchar(50)
Declare @rnode Varchar(50)
Declare @result INT
Select @lnode=LeftChild,@rnode=RightChild From Tree where ParentId=@node;
if(@node='null')
set @result=0;
if(@lnode='null' AND @rnode='null')
set @result=1;
else
Set @result=vainimarketing.Countnode(@lnode)+vainimarketing.Countnode(@rnode);
return @result;
END
Posted 18-Dec-12 22:29pm

1 solution

Try ::
WITH CTE_Node(
  NodeID,
  NodeRigth,
  NodeLeft,
  Level,
  RigthOrLeft
  )
AS
(
SELECT 
 NodeID,
  NodeRigth,
  NodeLeft,
  0 AS Level,
  'P'
  FROM Node
  WHERE NodeID = 1
 
  UNION ALL
 
  SELECT 
 Node.NodeID,
  Node.NodeRigth,
  Node.NodeLeft,
  Level + 1,
  CASE WHEN CTE_Node.NodeLeft = Node.NodeID THEN 'R' ELSE 'L' END
  FROM Node
INNER JOIN CTE_Node ON CTE_Node.NodeLeft = Node.NodeID
  OR CTE_Node.NodeRigth = Node.NodeID
  )
SELECT DISTINCT RigthOrLeft, 
COUNT(NodeID) OVER(PARTITION BY RigthOrLeft)  
FROM CTE_Node
  Permalink  
v3
Comments
rajan1189 at 19-Dec-12 4:15am
   
hello Kuthuparakkal ....
my tree is containg three column parentid,leftchild,rightchild,if any of the child is empty ,it is set to 'null' which is a string or varchar...
Kuthuparakkal at 19-Dec-12 4:21am
   
Okay posted new solution, Modify to match to your table name, column name etc..
rajan1189 at 19-Dec-12 6:02am
   
@Kuthuparakkal sir i never use this cte,i need to calculate tree leaves,In my table three column are there Parentid,leftchildid,rightchildid and i am using sql server 2008,and the given function is a scalar function,from this i m fetching the value in stored procedure,so please help me out if we can do it by function only ,,if not then please do chage it according ,,i need value return from function too..thanks in advance,,
rajan1189 at 19-Dec-12 6:03am
   
please do change in the existing function ..
rajan1189 at 19-Dec-12 6:02am
   
@Kuthuparakkal sir i never use this cte,i need to calculate tree leaves,In my table three column are there Parentid,leftchildid,rightchildid and i am using sql server 2008,and the given function is a scalar function,from this i m fetching the value in stored procedure,so please help me out if we can do it by function only ,,if not then please do chage it according ,,i need value return from function too..thanks in advance,,

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Dnyaneshwar@Pune 692
1 RyanDev 230
2 CHill60 163
3 Sascha Lefèvre 145
4 Sergey Alexandrovich Kryukov 124
0 Sergey Alexandrovich Kryukov 10,401
1 OriginalGriff 8,910
2 Sascha Lefèvre 3,899
3 Maciej Los 3,422
4 Richard Deeming 2,600


Advertise | Privacy | Mobile
Web04 | 2.8.150520.1 | Last Updated 19 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100