count nested tree user

id  left_User_ID  right_User_ID  referral_Id
--  ------------  -------------  -----------
1   2             5              1
2   3             4              1
3   null          null           2

I want to cacluate how many member are under user 1. for this i create a function that count left side only.

so开发者_JAVA技巧 the its goes like 1->2->3.so it count 2. same thing done for calculating right side.

so what to do for calculating all side means left and right

SELECT
   Sum(
   CASE WHEN left_user_ID is null THEN 0 ELSE 1 END +
   CASE WHEN right_user_ID is null THEN 0 ELSE 1 END
   )
FROM
   Table
WHERE
   referral_Id = 1

this is recursion...and Recursion can be possible only with cte in sql. I used MS SQL server 2005 . i try this for counting left side node HERE MY TABLE NAME IS LEG

1. create function left_legs(@id int)
2. RETURNS varchar(4) AS BEGIN
3. DECLARE @count int set @count=0 WHILE @id !=0
4. BEGIN
5. set@id=cast((SELECT left_user_Id from Leg where id=@id)as varchar(4))
6. IF @id != 0
7. BEGIN
8. set @count =@count +'1'
9. END
10. END
11. RETURN @count
12. END
    This function count total left nodes.only in one direction.

Without a more specific definition of the problem, it's hard to give good help. However, it sounds like you're halfway there, if you can count the left-hand entries.

Rather than counting left-hand entries, try counting both sides. Psuedo-code for this looks something like this:

public static int leafCount(Node node) {
   if (node == null) {
       return 0;
   }

   return 1 + leafCount(node.left) + leafCount(node.right);
}

If whatever backend language you're using has a library for a tree structure, I would expect there is already a similar function provided.

After revealing that we do indeed have a tree table structure, queryable in SQL -

Table:

CREATE TABLE RECURSE (ID INT NOT NULL WITH DEFAULT, 
                      R_SIDE INT NOT NULL WITH DEFAULT, 
                      L_SIDE INT NOT NULL WITH DEFAULT)   

Data:

INSERT INTO RECURSE VALUES (1, 2, 5)
                           (2, 3, 4)
                           (3, 0, 0)
                           (4, 0, 0)
                           (5, 0, 0)

Resulting setup:

Id    R_Side    L_Side
 1    2         5
 2    3         4
 3    0         0
 4    0         0
 5    0         0

This statement will should return the count of the left and right nodes. Under DB2 at the least, it seems that there are restrictions that make left joins difficult to employ inside recursive CTEs, so you have to count the left and right side separately. Use this as a starting point, if it doesn't exactly meet your needs.

WITH left_side (id, count) as (SELECT id, 0
                               FROM recurse
                               WHERE L_Side = 0
                               UNION ALL
                               SELECT a.id, 1 + b.count
                               FROM recurse as a
                               JOIN left_side as b
                               ON b.id = a.l_side
                               where a.l_side > 0),

     right_side (id, count) as (SELECT id, 0
                                FROM recurse
                                WHERE R_Side = 0
                                UNION ALL
                                SELECT a.id, 1 + b.count
                                FROM recurse as a
                                JOIN right_side as b
                                ON b.id = a.R_Side
                                where a.l_side > 0)

SELECT a.id, COALESCE(b.count, 0) + COALESCE(c.count, 0) as LeafCount
FROM recurse as a
LEFT JOIN right_side as b
ON b.id = a.id
LEFT JOIN left_side as c
ON clid = a.id

Yields:

Id     LeafCount
 1     3
 2     2
 3     0
 4     0
 5     0