SELECt d.`iD`, d.`subsectionOf`, CONCAt(REPEAT('-', p.`len`), d.`name`) as hier, p.`len`, p.`ancestor`, p.`descendant`, GROUP_CONCAt(crumbs.`ancestor`) AS breadcrumbsFROM `TreeData` AS dJOIN `TreePaths` AS p ON d.`iD` = p.`descendant`JOIN `TreePaths` AS crumbs ON crumbs.`descendant` = p.`descendant`WHERe p.`ancestor` = 1GROUP BY d.`iD`ORDER BY breadcrumbs;+----+--------------+---------------------+-----+----------+------------+-------------+| iD | subsectionOf | hier | len | ancestor | descendant | breadcrumbs |+----+--------------+---------------------+-----+----------+------------+-------------+| 1 | NULL | Root A | 0 | 1 | 1 | 1| | 2 | 1 | -Item 1 | 1 | 1 | 2 | 1,2 | | 5 | 2 | --Item 1 Sub Item 2 | 2 | 1 | 5 | 1,2,5 | | 6 | 2 | --Item 1 Sub Item 1 | 2 | 1 | 6 | 1,2,6 | | 3 | 1 | -Item 2 | 1 | 1 | 3 | 1,3 | | 4 | 1 | -Item 3 | 1 | 1 | 4 | 1,4 | | 8 | 4 | --Item 3 Sub Item 1 | 2 | 1 | 8 | 1,4,8 | | 9 | 4 | --Item 3 Sub Item 2 | 2 | 1 | 9 | 1,4,9 | | 7 | 1 | -Item 4 | 1 | 1 | 7 | 1,7 | +----+--------------+---------------------+-----+----------+------------+-------------+
