Help with "Connect By"

From: Alan Taylor <aet_at_athena.demon.co.uk>
Date: 1997/05/25
Message-ID: <DqXmhBA65AizEwGt@athena.demon.co.uk>#1/1

I need to traverse backwards through a tree-style table and thought that Connect By would be the obvious method, but am having considerable difficulty in getting it to work. Can anyone help?

The table has the following structure:

create table TEST (

   Node_Id     Number(10)     not null,
   Section_Id  Number(10)     not null,
   Parent_Id   Number(10)     not null,

   Description VarChar2(200) not null
)

   constraint Test_PK primary key (Node_Id, Section_Id) /

The table contains data along the lines of:

Node_Id Section_Id Parent_Id Desc
------- ---------- --------- ----

      1 0 0 Buildings -- Root node

      1          1         0 Commercial    -- Build.
      1          2         0 Residential   --   "

      1          3         1 Office        -- Build.\Comm.
      1          4         1 Shop          --   "      "
      1          5         1 Farm          --   "      "

      1          7         2 Apartment     -- Build.\Res.
      1          8         2 House         --   "     "

      1          9         7 1 Bedroom     -- Build.\Res.\Apart
      1         10         7 2 Bedroom     --   "     "     "
      1         11         7 3 Bedroom     --   "     "     "

      1          9         8 1 Bedroom     -- Build.\Res.\House
      1         10         8 2 Bedroom     --   "     "     "
      1         11         8 3 Bedroom     --   "     "     "

      2          0         0 Vehicles      -- Root node

      2          1         0 Commercial    -- Vehicles
      2          2         0 Private       --    "

      2          3         1 Truck         -- Vehicles\Commercial
      2          4         1 Van           --    "          "

etc...

The Node_Id is generated by a Sequence. The root node always has a Section_Id and Parent_Id of 0.
The Section_Id is unique within the Node_Id (generated by max(Section_Id) + 1 where Node_Id = <whatever> ) The Parent_Id points to the Section_Id of it's "owner" to provide a tree structure.

The query that I'm trying to execute needs to extract the tree structure leading to a specified item, i.e. find everything LIKE "FA%" ...

Buildings
  Commercial
    Farm
    Factory

...or everything LIKE "Bedroom%" ...

Buildings
  Residential
    Apartment

      1 Bedroom
      2 Bedroom
      3 Bedroom
    House
      1 Bedroom
      2 Bedroom
      3 Bedroom

Finding the required item is OK, but the problem that I'm having is tracing back through the tree to find all the parent items leading down to it (Bearing in mind that the located item could occur at any level within the tree).

I know that this is a long-winded question and if you've made it this far then I thank you for your patience <g>.

-- 
Alan

Received on Sun May 25 1997 - 00:00:00 CDT