Q

SQL query for parent/child relationship

I am working on Oracle HRMS and Payroll Reports, and I'm developing several reports like pay slip and cash register, etc. As the organization consists of one parent and several child/sister concerns, then each child has several departments and divisions of its own. I need to develop reports that include parameters like parent organization name and child/sister organization name, and display the data according to the level of organization the user selects.

For instance, if the user selects top most organization in hierarchy, the pay slips of all the children and sub children must be displayed in the same way if user selects a child organization then pay slips of that child and all of its sub children must be displayed.

I just need to write the query for getting this parent/child relationship on SQL, according to user input.
Well, in order to display PARENT CHILD Relationship you can use the CONENCT BY statement... You can also use an intrinsic column called "LEVEL" in your SQL statement to provide indentation to your output.

Let's say you have an organization hierarchy table like this:

ORGANIZATION_HIEARARCHIES
(ORG_HIER_ID NUMBER NOT NULL,
 CHILD_ORGANIZATION_ID   NUMBER NOT NULL,
PARENT_ORGANIZATION_ID   NUMBER)

You also have an ORGANIZATIONS table that is something like this:

ORGANIZATION
(ORGANIZATION_ID  NUMBER NOT NULL,
 ORGANIZATION_NAME  VARCHAR2(100) NOT NULL)

ORGANIZATION_ID is a foreign key to both CHILD_ORGANIZATION_ID column and PARENT_ORGANIZAITON_ID column.

Anyway, to provide a hierarchy that starts with a child_organization_id and displays all of its children and grand children you can use the following SQL statement.

SELECT  t1.organization_id,
                RPAD (' ', 3*(level-1))||t1.organization_name displayed_organization
FROM    organizations t1,
               organization_hierarchies t2
WHERE  t2.child_organization_id = t1.organization_id
CONNECT BY t2.parent_organization_id = prior t2.child_organization_id
 START WITH t2.child_organization_id = :PARAMETER_ORG_ID
 ORDER SIBLINGS BY SORT_ORDER

Please note that the ORDER SIBLINGS BY SORT_ORDER clause is a new feature in Oracle9i SQL that enables you to order siblings in a hierarchical tree that was not available in earlier version of Oracle.

This was first published in March 2004

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close