RE: Tricky Hierarchical Query

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: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