db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Alain_Rastoul" <alr....@free.fr>
Subject Re: Recursive query on common table
Date Wed, 14 Oct 2009 21:46:24 GMT
Hi Vrin26

You can represent your "tree" as an interval with each node of the tree 
within the interval of his parent node .
Lets say each node has a name, a parent id, plus a left id and a right id 
(left and right bounds of the interval).
Each child node must have a left and right id values between the left and 
right id values of his parent node,
doing this way you can select the whole tree of all childs of a node with a 
single select.
The trick is to maintain the left and right id values consistent with 
triggers each time you insert, updatge , delete  data in your table, 
recompute the
left and right boudns using the parent id .

something like :
select node.id, node.name
from node
where node.leftId >= parent.leftId
and node.rightId <= parent.rightId

for a given parent node
(you can join it in the select)

Hope it helps

regards

Alain


"Vrin26" <divi.markapuram@in.ibm.com> a écrit 
dans le message de news: 25885395.post@talk.nabble.com...

Sylvain,

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.

Thanks.


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.





Mime
View raw message