Trees of known depth

from the Artful Common Queries page


A tree is a hierarchy where each node except the root has one parent. A parent-child link is an edge. Edges in a tree of known depth can be queried with n-1 self-joins where n is the number of edges from top to bottom. Here is a simple example of depth 2:

drop table if exists t;
create table t(id int, parent int, ord int, title char(20));
insert into t values
(1, 0, 0, 'Root'),
(2, 1, 0, 'Home'),
(3, 1, 1, 'Projects'),
(5, 1, 2, 'Secret area'),
(4, 1, 3, 'Tutorials'),
(8, 1, 4, 'Example'),
(6, 4, 0, 'Computing'),
(7, 4, 1, 'Life');

To list children under their parents, self-join on left parent = right id and order on left parent, right ord:

SELECT t1.id,t1.parent,t2.ord,t1.title,t1.title as parentTitle
FROM t t1
LEFT JOIN t t2 ON t1.parent=t2.id
ORDER BY t1.parent,t2.ord;
+------+--------+------+-------------+-------------+
| id   | parent | ord  | title       | parentTitle |
+------+--------+------+-------------+-------------+
|    1 |      0 | NULL | Root        | Root        |
|    8 |      1 |    0 | Example     | Example     |
|    2 |      1 |    0 | Home        | Home        |
|    3 |      1 |    0 | Projects    | Projects    |
|    5 |      1 |    0 | Secret area | Secret area |
|    4 |      1 |    0 | Tutorials   | Tutorials   |
|    6 |      4 |    3 | Computing   | Computing   |
|    7 |      4 |    3 | Life        | Life        |
+------+--------+------+-------------+-------------+

For trees of unknown depth see http://artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

Return to the Artful Common Queries page