By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,966 Members | 2,054 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,966 IT Pros & Developers. It's quick & easy.

Count the downline node in tree using store procedure in mysql

P: 2
Hi all.
I have a table in Mysql. The table consists of three columns: SponsorID, LeftID and RightID. And the table consists of hierarchical data, exactly like a binary tree.

LeftID and RightID can become SponsorID 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 a SponsorID. A simple select statement just returns three rows. Please, give me the solutions.

SponsorID LeftID RightID ReferenceID ParentID
1 2 3 0 0
2 4 5 1 1
3 6 7 1 1
4 8 9 2 2
8 0 0 2 4
5 10 11 1 2
10 12 0 1 5
6 13 0 1 3
9 0 0 1 4
7 0 0 1 3
11 14 15 3 1
16 22 23 3 1
13 16 17 8 3
14 18 19 5 2
15 20 21 3 8
22 0 0 2 16
Oct 1 '11 #1
Share this Question
Share on Google+
2 Replies


patjones
Expert 100+
P: 931
I sort of understand what you need, but a concrete example would help. Are you able to give an example of how the left and right IDs become a sponsor ID and propagate this hierarchical relationship in the table?

Pat
Oct 3 '11 #2

Rabbit
Expert Mod 10K+
P: 12,364
The data structure you've chosen to store hierarchy data is fundamentally flawed in terms of querying.

To properly store hierarchical data in a binary tree and still allow querying, you need to use a power of two location based storage method. As demonstrated here. However, if you have many layers in your hierarchy, it can quickly overflow the data types available to you.

If you do have a lot of layers in your hierarchy, then I recommend the nested set or preordered tree. Which the author briefly mentions in the article.
Oct 3 '11 #3

Post your reply

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