Re: Recursive Queries

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.

[...]

>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
>=======================

[...]

>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.

Paul,

you can utilize the Oracle SQL feature for recursive queries to solve this problem. A possible query might look like this:

SELECT f.name
  FROM file f
 WHERE f.parent_dirid IN

      (SELECT d.directory_id
         FROM directory d
      CONNECT BY PRIOR d.directory_id = d.parent_dir_id
        START WITH d.name = <'given dir name'>);

This gives the problem however that with only the name of the directory from which you want to start the criterion for the 'start with' clause is not unique, I'm not sure if this is what you want (IMHO you would at least also need a unique constraint on (directory.name, directory.parent_dirid) and also on (file.name, file.parent_dirid). But if you want to start in a specific location, you will probably know the directory_id, so you could replace the START WITH clause with: START WITH d.directory_id = <your_dir_id> which would only search through one specific directory and it's complete substructure.

Some more examples regarding this SQL construct are also in the Oracle SQL Reference in the section about the SELECT statement.

HTH,
Peter

--
Peter Schneider
pschneider_at_knuut.de Received on Wed Apr 28 1999 - 18:37:10 CDT