Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <25885395.post@talk.nabble.com>
Date: Tue, 13 Oct 2009 22:06:29 -0700 (PDT)
From: Vrin26 <divi.markapuram@in.ibm.com>
To: derby-user@db.apache.org
Subject: Re: Recursive query on common table
In-Reply-To: <4AD33BB0.10801@wanadoo.fr>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
References: <25819772.post@talk.nabble.com>
 <205895.59196.qm@web31815.mail.mud.yahoo.com> <25850680.post@talk.nabble.com>
 <410150.10376.qm@web31809.mail.mud.yahoo.com> <4AD33BB0.10801@wanadoo.fr>


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 i=
n
this chain.
I will post my solution as and when it gets fixed.

Thanks.


Sylvain Leroux wrote:
>=20
> Geoff hendrey a =C3=A9crit :
>> SELECT parent.id, child.id FROM T as parent, T as Child WHERE=20
>> child.parent=3Dparent.id ORDER BY parent.id
>>=20
>> In the case where the tree is a doubly linked list, you'd get this
>>=20
>> 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=20
> here the problem is to find all the descendants (or ascendants) from a
> node.=20
> Regardless the number of intermediate levels.
>=20
> If you use adjacency lists, recursion is the answer. But is not directly=
=20
> supported by Derby. As Rick Hillegas suggested, one solution would be to=
=20
> encapsulate the recursive part or your query in a custom table function
> (written=20
> in Java). I've never done that, so if you do, I would find of great
> benefice if=20
> you post your solution on the mailing list (or the wiki)!
>=20
>=20
> Otherwise, there is an article on mysql.com that describe that exact kind
> of=20
> problem and propose a solution using nested sets instead. That way, you n=
o=20
> longer needs recursion:
> http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
>=20
>=20
> Hope this helps,
> Sylvain
>=20
> --=20
> Website: http://www.chicoree.fr
>=20
>=20
>=20
>=20

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