tree structures in database tables?

From: Andy Ganse <andy_ganse_at_psmfc.org>
Date: 1998/02/23
Message-ID: <34F2245F.593F@psmfc.org>#1/1

Hi all -
I'm looking for a SQL statement or PL/SQL procedure to output the ordered structure of a data tree held in my Oracle database table. Unfortunately it didn't end up as easy as I first thought and I'm looking for a hand.

Here's what I've got:
I've a table that sets up a kind of tree, with the following columns:   table REGIONS

---

  region_id      CHAR(4)
  region_name    VARCHAR2(20)
  region_group   CHAR(4)

The regions are hierarchical; for example I might have City of Seattle within King County within Puget Sound Area within State of WA, etc. So some table entries might look like this:

  REGION_ID   REGION_NAME          REGION_GROUP
  ---------   ----------------     ------------
  SEAT        City of Seattle      KING
  BELL        City of Bellevue     KING
  KING        King County          PUGS
  PUGS        Puget Sound Area     WAST
  WAST        State of Washington  null
  ORST        State of Oregon      null
  SOMC        Some County in OR    ORST

(My tree-depth is not constant, so I don't know at any given time how many generations I have - and siblings and children are added in wherever's necessary by adding a new record to the bottom of the list.)

What I first tried to list this out was something along the lines of:   SELECT a.region_name, b.region_name, c.region_name   FROM regions a, regions b, regions c

  WHERE a.region_group = NULL
    AND b.region_group = a.region_id
    AND c.region_group = b.region_id

  ORDER BY a.region_name, b.region_name, c.region_name

But the problem is, as I mentioned above, my tree-depth doesn't stay constant, while this statement requires a specific depth. Also, my tree-depth is also generally much deeper than the above example, making for a really icky SQL statement anyways. And that's where I'm stuck.

A guy in another newsgroup said he remembered some kind of Oracle extensions to handle tree-structured data in tables, but he didn't remember what they were and I can't seem to find anything about it in my manuals. Any of y'all familiar with that? Or have a good way through my problem?

Much thanks in advance!
Cheers,
-Andy Ganse Received on Mon Feb 23 1998 - 00:00:00 CST