RE: Tricky Hierarchical Query

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 31 Aug 2001 14:55:49 -0700
Message-ID: <F001.0037FF06.20010831145034@fatcity.com>

(P.S. - original requirement listed below)

The best I could come up with would be using connect by  and prior, as suggested by another poster, in two queries with the "minus" set operator.

e.g.

SQL> select * from account ;
ACCOUNT_ID    ROOT_ID  PARENT_ID
---------- ---------- ----------
         5
         6          5          5
         7          5          5
         8          5          6
         9          5          6
        10          5          7
        11          5          8
        12          5          8
        13          5          9
        14          5         10

10 rows selected.

SQL> -- first select gets the whole "tree"
SQL> --   starting with the "root" for account 8
SQL> -- second query gets 8 and its children
SQL> select account_id


  2  from account

  3  connect by prior account_id = parent_id

  4  start with account_id = (select root_id from account where account_id = 8)

  5  minus

  6  select account_id

  7  from account

  8  connect by prior account_id = parent_id
  9  start with account_id = 8 ;

ACCOUNT_ID

---

         5
         6
         7
         9
        10
        13
        14

7 rows selected.

Jacques R. Kilchoër
x8816

 

> -----Original Message-----
> From: Walter K [mailto:alden14004_at_yahoo.com]
> Sent: vendredi, 31. août 2001 12:43
> To: Jacques Kilchoer; 'ORACLE-L_at_fatcity.com'
> Cc: 'alden14004_at_yahoo.com'
> Subject: RE: Tricky Hierarchical Query
> 
> 
> Sorry. I mis-stated the relationship to the parent in
> my original email. Parent_id is always populated
> unless the row is a root row.
> 
> The data for all rows in my sample tree would be:
> 
> ACCOUNT_ID   ROOT_ID   PARENT_ID
> 5            null      null
> 6            5         5
> 7            5         5
> 8            5         6
> 9            5         6
> 10           5         7
> 11           5         8
> 12           5         8
> 13           5         9
> 14           5         10
> 
> > > -----Original Message-----
> > > From: Walter K [mailto:alden14004_at_yahoo.com]
> > > 
> > > I am stumped on how to do a particular
> > hierarchical
> > > query. The query needs to be written entirely in
> > SQL.
> > > 
> > > I have a table (ACCOUNT) with 3 columns of
> > interest in
> > > it, ACCOUNT_ID, ROOT_ID, PARENT_ID. Every row
> > > represents an account. If an account is the "root"
> > > (top-most), then the ROOT_ID and PARENT_ID columns
> > are
> > > null. If an account is a "parent", then the
> > ROOT_ID is
> > > populated but the PARENT_ID is null. Accounts can
> > be
> > > nested multiple levels deep.
> > > 
> > > I need to find all of the accounts that belong to
> > the
> > > root of the given account but not include any
> > accounts
> > > that are children of the given account. Does this
> > make
> > > sense?
> > > 
> > > I.e.          5
> > >           6       7
> > >        8     9      10
> > >     11   12    13      14
> > > 
> > > I'm not sure if my tree diagram will be dispalyed
> > > properly after I email this but in essence if "8"
> > is
> > > the given account number, I want everything
> > returned
> > > except for 8, 11 and 12.

Received on Fri Aug 31 2001 - 16:55:49 CDT