0

I need to do a hierarchical query, and as an added output, I need to show the generation of each node in the data tree.

The maximal number of generations is known, but a node may be in different generations for different ancestors.

In the end, I need a table that shows all the descendants for each first-generation entry, possibly with entries appearing several times.

Starting table (example: route finder, max number of generations 3):

ID_object | name_object | ID_parent
-----------------------------------
1         | Bus Line 1  | NULL
2         | Bus Line 2  | 1
3         | Tram Line 1 | 1
4         | Train Line 1| 3
5         | Tram Line 2 | 6
6         | Train Line 2| NULL
7         | Bus Line 3  | 3

The resulting table should look like this:

ID_object | name_object | ID_ancestor | ID_parent | Generation
1         | Bus Line 1  | 1           | NULL      | 1
2         | Bus Line 2  | 1           | 1         | 2
3         | Tram Line 1 | 1           | 1         | 2
4         | Train Line 1| 1           | 3         | 3
7         | Bus Line 3  | 1           | 3         | 3
6         | Train Line 2| 2           | NULL      | 1
5         | Tram Line 2 | 2           | 6         | 2

How do I calculate the generation efficiently? My first thought, coming from more procedural programming, as a recursion, but that doesn't work in MySQL...

JLN
  • 3
  • 3
  • How many max generations (levels) may exist? Or is that number unlimited? – TJ- Aug 20 '14 at 08:08
  • There's plenty of material on "mysql transitive closure" on the net. Have a look at some of it. | Instead of recursing you can still use looping in MySQL to iteratively traverse the tree's levels. – JimmyB Aug 20 '14 at 08:11
  • @TJ- The number of generations may depend on the table being processed, but is not in general unlimited; the information is usually also stored in the original table. – JLN Aug 20 '14 at 08:21

0 Answers0