473,545 Members | 2,173 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

68 New Member
[IMG]C:\Documents and Settings\oOo\De sktop\Introduce r 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.StoredProce dure1
(
@parentID int
)

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


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

Please help me. I m in urgent. Thanks in advance.
May 7 '08 #1
6 7508
deepuv04
227 Recognized Expert New Member
[IMG]C:\Documents and Settings\oOo\De sktop\Introduce r 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.StoredProce dure1
(
@parentID int
)

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


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.StoredProce dure1
(
@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.LeftCustomer ID,C.RightCusto merID)
)
SELECT IntroducerID, LeftCustomerID, RightCustomerID from cte
END
[/code]

thanks
May 9 '08 #2
sangam56
68 New Member
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.
May 11 '08 #3
deepuv04
227 Recognized Expert New Member
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.StoredProce dure1
(
@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.LeftCustomer ID,C.RightCusto merID)
)
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.LeftCustomer ID,C.RightCusto merID)
)
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.LeftCustomer ID,C.RightCusto merID)
)
SELECT count(*) as LeftDescendents from cte



END
[/code]
May 12 '08 #4
sangam56
68 New Member
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.
May 13 '08 #5
deepuv04
227 Recognized Expert New Member
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.asp x

http://www.vb-helper.com/howto_net_fract al_binary_tree. html

thanks
May 13 '08 #6
sangam56
68 New Member
Thanks deepuv04.
I followed your suggestions and got good ideas.
I am very grateful to you. Thanks a lot.
May 14 '08 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

3
1734
by: rob.guitar.rob | last post by:
Hello, My last few posts have been revolving aroung the same problem, and I still cant solve it and I would be really appreciate if anyone could spot a problem. a section of my XML goes like ..... <parent>
7
2199
by: abhrajit | last post by:
I'm looking for a C/C++/Java library to create a balanced binary tree data structure given a set of leaf nodes as input. A leaf node should never become an interior node. So if I wish to create a tree that will have a,b,c & d as leaf nodes - this tree will contain nodes other than a,b,c & d as interior nodes: e.g. x / \
12
20509
by: Dino L. | last post by:
I am putting data from DataTable to treeView foreach( DataRow aRow in aTable.Rows) { TreeNode tnode = new TreeNode(aRow.ToString() + aRow.ToString() + " " + aRow.ToString()); treeView1.Nodes.Add(tnode); //till here code works fine //now I wanna add child nodes for last inserted node foreach( DataRow GrupaRow in TabelaGrupe.Rows)
8
4265
by: Invalidlastname | last post by:
Hi, We are developing an asp.net application, and we dynamically created certain literal controls to represent some read-only text for certain editable controls. However, recently we found an issue which is related to the repeater. In the code shown below, if I call Repeater1.Controls.Count in the OnInit (the code fragment was highlighted in...
1
2106
by: Eric | last post by:
I created a simple web site in VS 2005 and then copied it underneath the IIS tree and created a virtual directory for it. When I try to access the default.aspx page I get the below error text: Note that this is the default web.config file generated by the application. The OS is Windows Server 2003, released version of VS 2005, created an...
7
3753
by: amruta | last post by:
the code below dows not let me get the parent child view... all the nodes are show in one line only... also i need them to be collasped ... Thanks ..
2
1963
by: Luqman | last post by:
I have filled a treeview with parents and childs, using Hashtables. Now, how can I check the required Parent and its Child Nodes. for example, I have following data Group Item A Item A1 Item A2
10
34441
by: John Rogers | last post by:
This code only counts the parent nodes or rootnodes in a treeview, how do you count all the nodes in a treeview? // one way int NodeCounter = 0; foreach (TreeNode currentNode in TreeView1.Nodes) NodeCounter++; // other way int total = TreeView1.Nodes.Count;
0
2605
by: kajuchirag | last post by:
How do i store the date and tree root node and child nodes in database. How many tables to be created. It should be fast in counting the nodes even if there are more than 25000 child nodes in left or right.
0
7934
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7446
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7778
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6003
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
4966
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3476
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3459
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1908
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
731
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.