Sylvain Leroux wrote:
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)!
If you do pursue this approach, you may save yourself some time by
reading the white paper on table functions posted here:
http://developers.sun.com/javadb/reference/whitepapers/index.jsp In
particular, you should be able to extend EnumeratorTableFunction, a
handy wrapper class which does most of the work required to turn an
arbitrary Enumeration, Iterator, or Iterable into a table function.
Hope this helps,
-Rick
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
