As a response to this question, Bill Karwin posted the following query to pull back an ordered sub-tree given any particular tree-node-id:
SELECT d.`iD`, d.`subsectionOf`,
CONCAT(REPEAT('-', p.`len`), d.`name`) as hier,
p.`len`, p.`ancestor`, p.`descendant`,
GROUP_CONCAT(crumbs.`ancestor`) AS breadcrumbs
FROM `TreeData` AS d
JOIN `TreePaths` AS p ON d.`iD` = p.`descendant`
JOIN `TreePaths` AS crumbs ON crumbs.`descendant` = p.`descendant`
WHERE p.`ancestor` = 1
GROUP BY d.`iD`
ORDER BY breadcrumbs;
I myself have successfully used this query in a couple of my own projects, but I realized that I don't understand exactly how and why this query works. I feel I have a decent understanding of the closure table design (primarily through reading Karwin's deck on SQL handling hierarchical data). Nonetheless, I am not comfortable that I can "finger trace" this query and explain how it is constructing the result set.
Specifically, I am having the most trouble understanding the second JOIN on the "treePaths" table (which I understand to be the actual closure table that records the ancestor and descendent relationships for each node/leaf). How does this create the breadcrumbs result?
