db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Geoff hendrey <geoff_hend...@yahoo.com>
Subject Re: Recursive query on common table
Date Mon, 12 Oct 2009 13:51:53 GMT
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.


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?


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.
View raw message