Re: Querying Hierarchical Table

On Wed, 12 Dec 2001 10:46:08 +1100, sweidanz_at_yahoo.com wrote:

>I am trying to think of an efficient way to write a query on a
>hierarchical table.
>The table is as follows:
>
>locality_id locality_parent locality_desc
>1 0 NSW
>2 1 Sydney
>3 2 Hunter Street
>
>I need a query to display the above three levels in one line. 3 levels is
>the maximum and most likely to be.
>
>I thought of the query as follows:
>
>
>SELECT loc3.locality_desc|| ' '|| loc2.locality_desc || ' '||
>loc1.locality_desc AS loc_address
>FROM locality loc1,
> locality loc2,
> locality loc3
>WHERE
> loc3.locality_id = 3 AND
> loc3.locality_parent = loc2.locality_id AND
> loc2.locality_parent = loc1.locality_id AND
>
>
>Does anyone have any other more efficient way of writing the query?
>
>Thanks,
>ZS

It believe matters what version of Oracle you are using.

You most probably want a heirarchical query, which uses the CONNECT BY clause of the SELECT statement (check the SQL reference for details).

As far as wanting the entire tree on one line, I believe I saw an option posted before on how to do that in heirarchical queries, but I do not remember what it was, or which version of Oracle was needed to support it.

Brian Received on Wed Dec 12 2001 - 07:26:57 CST