Return the level and count of a closure table.
Hi, I have a table with the hierarchal closure model.
(my_tree)
+-------------+---------------+
| ancestor_id | descendant_id |
+-------------+---------------+
| 1 | 2 |
+-------------+---------------+
| 1 | 3 |
+-------------+---------------+
| 1 | 4 |
+-------------+---------------+
| 1 | 5 |
+-------------+---------------+
| 1 | 6 |
+-------------+---------------+
| 1 | 7 |
+-------------+---------------+
| 2 | 3 |
+-------------+---------------+
| 2 | 5 |
+-------------+---------------+
| 2 | 6 |
+-------------+---------------+
| 3 | 5 |
+-------------+---------------+
| 3 | 6 |
+-------------+---------------+
| 4 | 7 |
+-------------+---------------+
What I'd like to do is be able to return a result set based of this table like:
(result set)
+-------+-------+
| level | count |
+-------+-------+
| 1 | 2 |
| 2 | 2 |
| 3 | 3 |
| 4 | 0 |
+-------+-------+
Where the level column is the number of levels from the top of the tree. And count is the number of immediate children of all the nodes of that level. In the figure above, the first row has 1 and level and 2 as count. What this means is that all the nodes in the tree that are at level 1 have 2 as the sum of their immediate children.
Tree format of the data above
lvl 1| 1
/ \
lvl 2| 2 4
| |
lvl 3| 3 7
/ | \
lvl 4| 5 8 6
At level 1 we have a count of 2 nodes (not 7 which is all the children of level one). Level 2 has sum of 2 also, because it's the sum of immediate children for EVERY node on that level.
So this is what I'm trying to accomplish the the table data I have above (my_tree). How can I do this?
If I didn't make myself clear and you'd like for me to explain more, please don't hesitate to post. I've been working on this for over a day, racking my brain, and am crying out to the MySQL community for help.
Thank you for taking the time in reading this post. I appreciate all the help I can get.
Edited 2 time(s). Last edit at 05/06/2009 01:43PM by Sam Holmes.