• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1447
  • Last Modified:

MySQL: How to elegantly do recursion

I have a table to represent essentially a dot notation "hierarchy" of labels.

ID     LEVEL     PARENT ID      NAME        DISPLAY ORDER(within level)
1          1                0                  A                          1
2          2                1                 one                       1
3          2                1                  two                     2
4          2                1                three                     3
5          3                2                   a                         1
6          3                2                   b                         2
25        1                 0                  B                         2

which would yield names of the form:

1.  I currently have a PHP loop which assumes that level "0" is a "root" and PHP recurses to "assemble" the names. This is okay but not very elegant.  Is there a recursion technique somewhere to accomplish this.

2. An easier question may be:  Given a leafnode ID, assemble the full hierarchical name.    I currently have a PHP routine that has the equivalent of a DO... UNTIL loop that traverses (drills down) and prepends to an accumulating string.    Again this is brute force, it seems like there should be an elegant solution within SQL to accomplish something like this.   I cannot find any pointers or book/manual chapters that discuss these topics.

Any ideas/tricks?

1 Solution
willsherwoodAuthor Commented:

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now