RelationalDBDesign  Tweet
RedhatLinuxSysAdmin SeoMining


Lesson 3 Hierarchical retrieval using CONNECT BY
Objective Write a query using the CONNECT BY feature.
When one record is related to another record in the same table, you have a recursive relationship. This kind of relationship is often called hierarchical.
A simple example of a hierarchical relationship is a family tree. In fact, because this is such an intuitive way of understanding a hierarchy, the relationship is often described as a parent/child relationship.
Hierarchical query: A form of query that retrieves data according to a hierarchy defined with a recursive relationship on a table. A parent/child list is produced that lists all the "children" items under their "parent" item.
Hierarchical relationship
In our sample pet store schema, you can see an example of a hierarchical relationship in the PRODUCT table. Some of the products are actually collections of other products. For example, the product called the Kitty Package is made up of a cat, a box of cat food, a bag of catnip, and a cat collar. Look at the FlipBook below to see how the hierarchical relationship between the Kitty Package product and its four products works.

Hierarchical Relationship

Hierarchical query
One of the challenges in working with hierarchical relationships is constructing a query that lists the parent with its children. This is particularly difficult if the relationship has more than one level. Fortunately, Oracle has come up with a special feature you can use to write a query that uses the hierarchy tree to pull data out of the table. Here is the general syntax:
SELECT column_name, column_name, ...
FROM table_name
CONNECT BY PRIOR parent_column_name = 
 child_column_name
START WITH condition;
The condition can be any valid condition you find in a WHERE clause. For example, if you only want to see what is in the Puppy Package, this query will do it:
SELECT PRODUCT_NAME, PRODUCT_ID
FROM PRODUCT
CONNECT BY PRIOR PRODUCT_ID = PACKAGE_ID
START WITH PRODUCT_NAME = 'Puppy Package';
The special phrases, CONNECT BY, PRIOR, and START WITH are used especially for a hierarchical query.

Hierarchy level
Oracle also lets you see the hierarchy level by adding the LEVEL pseudocolumn to your query. The figure below shows the query and the results.
Hierarchy Level
In the next lesson, you will compare the INTERSECT, MINUS, and UNION commands.
Hierarchical Retrieval - Exercise
Click the Exercise link below to practice writing queries.
Hierarchical Retrieval - Exercise