Count child nodes in tree like data table, SQL Server 2005

[sangam56]

[IMG]C:\Documents and Settings\oOo\Desktop\Introducer Table.jpg[/IMG] Hi all.
I have a table in sql 2005 management studio express edition. The table consists of three columns: IntroducerID, LeftCustomerID and RightCustomerID.
And the table consists of hierarchical data, exactly like a binary tree.

LeftCustomerID and RightCustomerID can become IntroducerID and have left and right child and this continues to create a hierarchical parent child relationship in the table.

Now I need to count the number of left and right child in the table when i pass an IntroducerID. A simple select statement just returns three rows.

Here is what I have written. It is a stored procedure.

Create Procedure dbo.StoredProcedure1
(
@parentID int
)

Select IntroducerID, LeftCustomerID, RightCustomerID
FROM Introducer
WHERE (IntroducerID=@parentID OR
IntroducerID=(Select LeftCutomerID FROM Introducer WHERE IntroducerID=@parentID) OR
IntroducerID=(Select RightCutomerID FROM Introducer WHERE IntroducerID=@parentID) );


The @parentID will be one of the IntroducerID in the Introducer table.

Please help me. I m in urgent. Thanks in advance.

[deepuv04]

[IMG]C:\Documents and Settings\oOo\Desktop\Introducer Table.jpg[/IMG] Hi all.
I have a table in sql 2005 management studio express edition. The table consists of three columns: IntroducerID, LeftCustomerID and RightCustomerID.
And the table consists of hierarchical data, exactly like a binary tree.

LeftCustomerID and RightCustomerID can become IntroducerID and have left and right child and this continues to create a hierarchical parent child relationship in the table.

Now I need to count the number of left and right child in the table when i pass an IntroducerID. A simple select statement just returns three rows.

Here is what I have written. It is a stored procedure.

Create Procedure dbo.StoredProcedure1
(
@parentID int
)

Select IntroducerID, LeftCustomerID, RightCustomerID
FROM Introducer
WHERE (IntroducerID=@parentID OR
IntroducerID=(Select LeftCutomerID FROM Introducer WHERE IntroducerID=@parentID) OR
IntroducerID=(Select RightCutomerID FROM Introducer WHERE IntroducerID=@parentID) );


The @parentID will be one of the IntroducerID in the Introducer table.

Please help me. I m in urgent. Thanks in advance.

Hi,
here you need to use recursive query.
try the following code

[code = sql ]
Create Procedure dbo.StoredProcedure1
(
@parentID int
)
as
BEGIN
with cte as
(
Select IntroducerID, LeftCustomerID, RightCustomerID
FROM Introducer
UNION ALL
Select IntroducerID, LeftCustomerID, RightCustomerID
FROM Introducer AS I,CTE C WHERE I.IntroducerID IN (C.LeftCustomerID,C.RightCustomerID)
)
SELECT IntroducerID, LeftCustomerID, RightCustomerID from cte
END
[/code]

thanks

[sangam56]

Thanks deepuv4,
The idea is marvellous. It worked.

Now I need to display the child nodes in an unbalanced binary tree. Is there any way to get around? Further, how to count the left and right descendents?

Many thanks again.

[deepuv04]

Thanks deepuv4,
The idea is marvellous. It worked.

Now I need to display the child nodes in an unbalanced binary tree. Is there any way to get around? Further, how to count the left and right descendents?

Many thanks again.

Hi,

1. For displaying all the child nodes added a where clause in the (IntroducerId = @ParentID).

2. For displaying left and right descendents i am taking the count of left subtree and right subtree seperately (same as the displaying all the child nodes) as the parentid is left node and right node respectively.

Here is the script you want.

[code = sql]
Create Procedure dbo.StoredProcedure1
(
@parentID int
)
as
BEGIN
-- For displaying all the chaild nodes
;with cte as
(
Select IntroducerID, LeftCustomerID, RightCustomerID
FROM Introducer WHERE IntroducerId = @ParentID
UNION ALL
Select IntroducerID, LeftCustomerID, RightCustomerID
FROM Introducer AS I,CTE C WHERE I.IntroducerID IN (C.LeftCustomerID,C.RightCustomerID)
)
SELECT IntroducerID, LeftCustomerID, RightCustomerID from cte

-- Count of left Descendents
;with cte as
(
Select IntroducerID, LeftCustomerID, RightCustomerID
FROM Introducer
WHERE IntroducerId = (SELECT LeftCustomerId FROM Introducer WHERE IntroducerId = @ParentID)
UNION ALL
Select IntroducerID, LeftCustomerID, RightCustomerID
FROM Introducer AS I,CTE C WHERE I.IntroducerID IN (C.LeftCustomerID,C.RightCustomerID)
)
SELECT count(*) as LeftDescendents from cte

-- Count of right Descendents
;with cte as
(
Select IntroducerID, LeftCustomerID, RightCustomerID
FROM Introducer
WHERE IntroducerId = (SELECT RightCustomerID FROM Introducer WHERE IntroducerId = @ParentID)
UNION ALL
Select IntroducerID, LeftCustomerID, RightCustomerID
FROM Introducer AS I,CTE C WHERE I.IntroducerID IN (C.LeftCustomerID,C.RightCustomerID)
)
SELECT count(*) as LeftDescendents from cte



END
[/code]

[sangam56]

Dear deepuv04,

Many many thanks for your help. It worked best for me. I now have both left and child node count.

And I have the count in label in asp.net web form. Is there any way to present the hierarchical data in graphical form. I mean can I present it in exact binary tree which may be unbalanced one. In fact, firstly I need to display the first three levels. When I click the leaf node, I should display the three levels below from this node. Please give me any idea. Lots of thanks in advance.

[deepuv04]

Dear deepuv04,

Many many thanks for your help. It worked best for me. I now have both left and child node count.

And I have the count in label in asp.net web form. Is there any way to present the hierarchical data in graphical form. I mean can I present it in exact binary tree which may be unbalanced one. In fact, firstly I need to display the first three levels. When I click the leaf node, I should display the three levels below from this node. Please give me any idea. Lots of thanks in advance.

Hi,
I have no idea about how to represent in graphical form in the web form ( it is .net related question)

here are two links probably will help you.

http://www.codeproject.com/KB/vb/SimpleBTree.aspx

http://www.vb-helper.com/howto_net_fractal_binary_tree.html

thanks

[sangam56]

Thanks deepuv04.
I followed your suggestions and got good ideas.
I am very grateful to you. Thanks a lot.