What would the data in your table look like, for the "tree" in the example?
e.g. 6 is a "child" of 5, but a "parent" of 8 and 9. Would the data look like this?
account_id root_id parent_id
5 null null
6 5 null
8 5 6
9 5 6
> -----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.
