Interesting. I figured you were trying a linked list or a tree. Would this work?

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

In my experience, table aliasing using the 'AS' keyword is the way to go.
 
-geoff


From: Vrin26 <divi.markapuram@in.ibm.com>
To: derby-user@db.apache.org
Sent: Sun, October 11, 2009 10:25:41 PM
Subject: Re: Recursive query on common table


Hi Geoffrey,

I am trying to create a hierarchy tree structure.
In my table I have columns Parent and Child.
Parent column can also have child ids and it's subchild ids in Child column.
I need to loop over table until i get chain of all parent, child, subchilds
and so on.
I don't have information about number of childs a parent can have, so I have
to loop until i don't get any more childs for parent.
Is there a way to achive this in Derby database?

Thanks




Geoffrey Hendrey wrote:
>
> Could you give some more detail on what the query will be used for?
>
>
> On Oct 9, 2009, at 4:52 AM, Vrin26 <divi.markapuram@in.ibm.com> wrote:
>
>
> Hi,
>
> I need to do a recursive query on a single derby database table.
> In Derby database documents it is mentioned that this derby doesn't
> support
> recursive queries.
> Is there any other work around to handle this scenario?
>
> Thanks
>
> --
> View this message in context:
> http://www.nabble.com/Recursive-query-on-common-table-tp25819772p25819772.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>
>
>

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