MySQL Forums

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.

Subject
Written By
Posted
Return the level and count of a closure table.
May 06, 2009 01:38PM
May 06, 2009 04:53PM
May 06, 2009 08:36PM
May 06, 2009 09:30PM
May 07, 2009 09:45AM
May 07, 2009 10:36AM
May 07, 2009 10:50AM
May 07, 2009 01:54PM
May 07, 2009 03:18PM
May 07, 2009 03:27PM
May 07, 2009 04:02PM
May 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.