db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vrin26 <divi.markapu...@in.ibm.com>
Subject Re: Recursive query on common table
Date Wed, 14 Oct 2009 05:06:29 GMT


What you had posted is exactly my problem.
I had already used same query which Geoffrey had mentioned but it doesn't
solve my problem
as I have to find all descendants.
Thanks for the information, i will go through all the solutions suggested in
this chain.
I will post my solution as and when it gets fixed.


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)!
> 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

View this message in context: http://www.nabble.com/Recursive-query-on-common-table-tp25819772p25885395.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

View raw message