Geoff hendrey a écrit :
SELECT parent.id, child.id FROM T as parent, T as Child WHERE
child.parent=parent.id ORDER BY parent.id
In the case where the tree is a doubly linked list, you'd get this
parent.id | child.id
1 2
2 3
3 4
4 5
With such a query, you could only find a direct descendant of a node. I think
here the problem is to find all the descendants (or ascendants) from a node.
Regardless the number of intermediate levels.
If you use adjacency lists, recursion is the answer. But is not directly
supported by Derby. As Rick Hillegas suggested, one solution would be to
encapsulate the recursive part or your query in a custom table function (written
in Java). I've never done that, so if you do, I would find of great benefice if
you post your solution on the mailing list (or the wiki)!
Otherwise, there is an article on mysql.com that describe that exact kind of
problem and propose a solution using nested sets instead. That way, you no
longer needs recursion:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
Hope this helps,
Sylvain
--
Website: http://www.chicoree.fr
