db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sylvain Leroux <s...@wanadoo.fr>
Subject Re: Recursive query on common table
Date Mon, 12 Oct 2009 14:22:40 GMT
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:

Hope this helps,

Website: http://www.chicoree.fr

View raw message