Building a query for Tree

[sreenath reddy]

Hi

In my application i have a folder tree where its stored in the Db in one table which maintains the parent id , folderid and the name of the folder

For example i have a folder structure like

a
--b
--c
--d

and the above will be stored in DB as


foldername folderid parentid

a 1 0(This is dummy id for the root folder)
b 2 1
c 3 2
d 4 1


and it goes on like that .......

Now given one folder id , i want to track the entire path from the root in single query .....

For eg given folderid 3 (folder is c) ....... i need to get that path as
a/b/c

is that possible to be achived in single query ?? its okay even if it can be achieved in 2-3 queries

[Shailesh Chandra]

It would be easy to form a query to get desired result.A folder can have any number of child folders hence one query would not suffice.

you can go for self join and in oracle connect by prior clause to form a query but You will have to play with java and queries both .


I haven't tried this but this is first thought in my mind

If I were to solve this problem I would have added one more column to database let say depth of folder 0 for root folder 1 for first child 2 for next and so on ....then I would have form a binary tree with delp of folder depth then a infix notation of binary tree would result in a path of folder.

can you tell me which database are you using ?

Shailesh

[sreenath reddy]

Hi sailesh

Thanks for ur reply.....i was knowing this connect by clause in Oracle 9..but i am using some DB(HSQL.Its a light weight JAVA DB) which supports only basic things (joins are also supported)

i think there is no way to do this with this DB in single query ...i need to club my javalogic with multiple DB queries

[Shailesh Chandra]

I think then you should go for couple of queries and take the advantages of maps in java and then you can form the folder path

Shailesh
[ May 25, 2005: Message edited by: Shailesh Chandra ]

[Derek Clarkson]

I have done something like this before. Firstly you have to design the data so that you can return it in the order in which you need to add the nodes.
So if you have

A
+-E
| +-D
| +-C
+-B
| +-F
G
+-H
...

Then you could assign a sort value which brings them back in the correct order.

Node Parent Sort
 A     -     1
 B     A     12
 C     E     112
 D     E     111
 E     A     11
 F     B     121
 G     -     2
 H     G     21

You need to get the data back using the sort. Then you simply add the nodes in that order, each time adding to the appropriate parent. because the sort ensures that each node appears after it's parent, the parents will always be ready regardless of the depth.

[Derek Clarkson]

Oh hang it. I just re-read your question. Are you talking about querying through the data ? By this a mean taking a specific node as a parameter to the sql and them tracing back to it's root, display each node in turn. If so then there is no way to do this in a single query that I can think of.