Querying Hierarchical Table

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 Received on Tue Dec 11 2001 - 17:46:08 CST