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" <[email protected]> a écrit dans le message de news: [email protected]... 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.
