Hi Jeremy, Is this an accurate example of your explanation?
folder id path 1 c:/folder1/ 2 c:/folder2/ file id id_folder name 1 1 file1.txt 2 1 file2.txt 3 2 file1.txt 4 2 file2.txt SELECT folder.path || file.name AS fullpath FROM folder,file WHERE file.id_folder = folder.id I get the correct results back for my requirement; which is exactly what I need, but I also have a great element of efficiency (yet I need to weigh this up against query complexity and query return time which is high on the format presented in my first post). This format uses more memory to store but the query is simple and query return time is relatively fast. (Still testing size relative to the other format) Please advice; Thank you for your help! Clive -----Original Message----- From: Jeremy Boynes [mailto:[EMAIL PROTECTED] Sent: 18 March 2005 06:52 PM To: Derby Discussion Subject: Re: Tree structure query A technique I have used for modeling filesystems and ldap structures is to split the tree into "folder" and "file" entities with the folder table containing denormalized paths. The denormalized path allows you to locate sub-trees with a LIKE match, with the pattern at the end so this is indexable. Just like in your model, each entity refers back to the folder that contains it giving you an arbitrary depth (up to the limit of the denormalized path). This allows you to query the folder structure easily (for a tree view) or to get the files in a folder fairly efficiently (leaf view). Moving folders is expensive as you need to rebuild the folder paths, but that tends to happen less frequently than file operations. -- Jeremy Clive Borrageiro wrote: > 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\ > > Any help will be very much appreciated! > Clive >
