MySQL Forums
Forum List  »  Newbie

Return the level and count of a closure table.
Posted by: Sam Holmes
Date: May 06, 2009 01:38PM

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.