| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Recursive Queries
There are two options, one of which is the "connect by" options. It has some
limitations, i.e. the "connect by" cannot be used in conjunction with another
table in a join. (It is possible to get around this with inline views.) The
second would be to use PL*SQL manage the hierarchy and treat the hierarchy as
a set of nesting sets. This is explained in "SQL for Smarties", I do not have the book handy right now. I have used this concept very successfullyand it is very flexible.
HTH
James
In article <37261D84.19D7AF13_at_geopak.com>,
Paul Schrum <Paul_at_geopak.com> wrote:
> I have a data structure which is a hierarchical tree represented in a
> relationional database. Without going into the specifics of my
> application, a directory/file structure is an exact cognate for it.
>
> Imagine something like File Manager or Windows Explorer in which there
> is a hierarchy of directories, and files under the directories. A given
> directory may have any number of files and any number of
> sub-directories. To represent this I have two tables: DIRECTORY and
> FILE. They appear as follows:
>
> =======================
> DESC DIRECTORY
>
> DIRECTORY_ID NUMBER(38) PRIMARY KEY
> NAME VARCHAR2(256)
> PARENT_DIR_ID NUMBER(38) FOREIGN KEY IS DIRECTORY.DIRECTORY_ID
> =======================
>
> DESC FILE
>
> FILE_ID NUMBER(38) PRIMARY KEY
> NAME VARCHAR2(256)
> PARENT_DIRID NUMBER(38) FOREIGN KEY IS DIRECTORY.DIRECTORY_ID
> =======================
>
> A sample of the data is as follows:
>
> Root
> |--config.sys
> |--Program Files
> | |--OraNT
> | | |--Oracle.exe
> | |--Games
> | | |--Freecell
> | | |--freecell.exe
> | | |--freecell.ini
> | |--Microsoft
> | |--word.exe
> |--autoexec.bat
>
> (Assume that any item with a file extension is a file. Else it is a
> directoy)
> Any directory can have an unlimited number of files and directories
> underneath it. There is no limit to the depth of directory nesting.
>
> SELECT * FROM DIRECTORY
> returns
> DIRECTORY_ID NAME PARENT_DIR_ID
> ------------ -------------- --------------
> 1 Root NULL
> 2 Program Files 1
> 3 OraNT 2
> 4 Games 2
> 5 Microsoft 2
> 6 Freecell 4
>
> SELECT * FROM FILE
> returns
> FILE_ID NAME PARENT_DIRID
> -------- ------------------- -------------
> 1 config.sys 1
> 2 Oracle.exe 3
> 3 freecell.exe 6
> 4 freecell.ini 6
> 5 word.exe 5
> 6 autoexec.bat 1
>
> The Question:
> How do I compose a query which, given a DIRECTORY.NAME, returns all
> files underneath that directory at all depths? I want only the files.
> I do not need the subdirectories in the final resutl.
>
> As I understand it, a simple subquery is not adequate because of the
> constraints I set above -- no limit to nesting depth or number of
> subitems.
>
> BTW, when I populate these tables on the client side in C, I use a
> recursive function call. This question, then, could be viewed as asking
> whether there is an sql syntax for a recursive query, and what would
> that be.
>
> To all who have made it this far in my post, I thank you. If you are
> able to respond, either here on usenet or directly to me via email is
> ok.
>
> -Paul Schrum
> GEOPAK Corp.
>
> paul_at_geopak.com
>
>
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Apr 28 1999 - 08:48:32 CDT
 |
 |