Mega Code Archive
Hierarchical Queries
<![CDATA[
Using the CONNECT BY and START WITH Clauses
The syntax for the CONNECT BY and START WITH clauses of a SELECT statement is as follows:
SELECT [LEVEL], column, expression, ...
FROM table
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
where
LEVEL is a pseudo-column that tells you how far into a tree you are.
LEVEL returns 1 for a root node, 2 for a child of the root, and so on.
start_condition specifies where to start the hierarchical query from.
You must specify a START WITH clause when writing a hierarchical query.
An example start_condition is employee_id = 1, which specifies the query starts from employee #1.
prior_condition specifies the relationship between the parent and child rows.
You must specify a CONNECT BY PRIOR clause when writing a hierarchical query.
An example prior_condition is employee_id = manager_id, which specifies the relationship is between the parent employee_id and the child manager_id-that is, the child's manager_id points to the parent's employee_id.
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813
]]>