db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <be...@sun.com>
Subject Re: Tree structure query
Date Fri, 18 Mar 2005 12:22:03 GMT
>>>>>>>>>>>> Clive Borrageiro wrote (2005-03-18 11:13:49):
> Hi,
> 
> I have a table that stores my filesystem entries in a structure that
> contains parent and child entries and the child entry contains its parent
> id.
> 
> Eg.
> 
> id	id_parent	path
> 1	0		c:
> 2	1		folder1
> 3	2		file1.txt
> 4	2		file2.txt
> 5	2		folder1_1
> 6	1		folder2
> 
> I need a query to do the Oracle equivalent of:
> "SELECT name, id, id_parent FROM table1 START WITH id = 1 CONNECT BY id =
> id_parent" -or something like that.
> 
> To get the result like this:
> C:\
> C:\folder1\
> C:\folder1\file1.txt
> C:\folder1\file2.txt
> C:\folder1\folder1_1\
> C:\folder2\
> 

With fixed number of levels (e.q. 4), you could do something like:

create view level1 (id, path) as 
       select id, path from tab where id_parent=0;
create view level2 (id, path) as 
       select distinct tab.id, {fn concat({fn concat(level1.path,'\')} ,tab.path)} from 
              level1, tab where level1.id = tab.id_parent;
create view level3 (id, path) as 
       select distinct tab.id, {fn concat({fn concat(level2.path,'\')} ,tab.path)} from 
              level2, tab where level2.id = tab.id_parent;
create view level4 (id, path) as 
              select distinct tab.id, {fn concat({fn concat(level3.path,'\')} ,tab.path)}
from 
                     level3, tab where level3.id = tab.id_parent;

select * from level1 union select * from level2 union select * from level3 union select *
from level4;


And if using Java & JDBC, create new levels as long as count(*) of
previous level is larger than 0;

> Any help will be very much appreciated!
> Clive

-- 
Bernt Marius Johnsen, HA Data Management, Sun Microsystems, Norway
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NOTICE: This signature is neither personal, religious, literary,
ethnic, racial, offensive nor humorous. 

Mime
View raw message