db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Recursive query on common table
Date Mon, 12 Oct 2009 14:40:05 GMT
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,
> 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

View raw message