(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:[EMAIL PROTECTED]]
> Sent: vendredi, 31. août 2001 12:43
> To: Jacques Kilchoer; '[EMAIL PROTECTED]'
> Cc: '[EMAIL PROTECTED]'
> 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:[EMAIL PROTECTED]]
> > >
> > > 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.
