How in 1 request to get all branch with childs?

[mstdmstd]

Hello,
Having table with fields id, parent_id, title (mysql5) how can I with 1 request to get all branch with childs
of parent N ? I mean grandchilds and deeper too…

Thanks!

[Thallius]

Relational databases are not designed for recursive problems but at the end it is of course possible.

Here is a very good explanation of the possibilities and pro and contra of them:

How to create a MySQL hierarchical recursive query?

[r937]

@Thallius

Here is a very good explanation

that is pretty good

it’s too bad @mstdmstd is on MySQL 5, because of course the best solution uses the recursive CTE

here’s my article on one of the other methods, using self-joins – Categories and Subcategories

regarding depth of the tree, i’d like to draw attention to these three points –

What if the hierarchy is more than, say, three or four levels deep? What if it’s fifteen levels deep? My response to this question is threefold.

First, a query with fifteen self-joins may be a little more tedious to code but most assuredly will not present any difficulty to your database engine.

Second, in certain databases such as Oracle and DB2, recursion is built in, so you can go as many levels deep as you wish—although don’t fool yourself, the coding required to display an arbitrary number of levels is no picnic either. Do not make the mistake of simulating recursion by coding a script module that calls itself, because from the database perspective, this is a series of calls (a query in a loop) and the performance will reflect this.

Thirdly, if you have a tree that goes more than three or four levels deep, you may have difficulty conveying this structure satisfactorily in a visual way. [emphasis added] You may want to go back and re-think how you expect your users to actually navigate through the hierarchy. Sometimes the best solution is simply to show no more than three levels, with some sort of visual clue that there are further levels below the nodes shown.