MySQL Forums :: Newbie :: Return the level and count of a closure table.


Advanced Search

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


Subject Written By Posted
Return the level and count of a closure table. Sam Holmes 05/06/2009 01:38PM
Re: Return the level and count of a closure table. Peter Brawley 05/06/2009 04:53PM
Re: Return the level and count of a closure table. Sam Holmes 05/06/2009 08:36PM
Re: Return the level and count of a closure table. Peter Brawley 05/06/2009 09:30PM
Re: Return the level and count of a closure table. Sam Holmes 05/07/2009 09:45AM
Re: Return the level and count of a closure table. Peter Brawley 05/07/2009 10:36AM
Re: Return the level and count of a closure table. Sam Holmes 05/07/2009 10:50AM
Re: Return the level and count of a closure table. Peter Brawley 05/07/2009 01:54PM
Re: Return the level and count of a closure table. Sam Holmes 05/07/2009 03:18PM
Re: Return the level and count of a closure table. Peter Brawley 05/07/2009 03:27PM
Re: Return the level and count of a closure table. Sam Holmes 05/07/2009 04:02PM
Re: Return the level and count of a closure table. Sam Holmes 05/07/2009 05:31PM


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.