	AllExperts > Oracle
	Search

Volunteer
Answers to thousands of questions

Home · More Oracle Questions · Answer Library

· Encyclopedia ·

Oracle - How to get all the childs,subchilds of parent record

Expert: Elliot Mak - 6/15/2009

Question
Hi Expert,

My requirement is to get all child, subchild of parent record.

Consider this explanation:
I want all the form functions attached to responsibility.
Generally form functions are attached to menus and menus are attached to responsibility.I have a input parameter as responsibility name. I am getting immediate child records but I have to get subchild of child records also and it can go to any level.

Can you please suggest me way to write SQL Query to get all the childs,subchilds of parent record.

Answer
Good day Roshan

To get the parent, child, and subchild is pretty straight forward with SQL. In the example below, there are
1. Three tables (Parent, Child, and Subchild tables)
2. Data inserted for all the three tables
3. SQL statement to retrieve all child, and subchild for all the parent

************************
Example starts here
************************

ORACLE_DB1 (ALLEXPERTS) > create table parent_table
2 (parent_id NUMBER
3 , parent_info VARCHAR2(64));

Table created.

Elapsed: 00:00:00.09
ORACLE_DB1 (ALLEXPERTS) >
ORACLE_DB1 (ALLEXPERTS) > create table child_table
2 (parent_id NUMBER
3 , child_id NUMBER
4 , child_info VARCHAR2(64));

Table created.

Elapsed: 00:00:00.07
ORACLE_DB1 (ALLEXPERTS) >
ORACLE_DB1 (ALLEXPERTS) > create table subchild_table
2 (parent_id NUMBER
3 , child_id NUMBER
4 , subchild_id NUMBER
5 , subchild_info VARCHAR2(64));

Table created.

Elapsed: 00:00:00.01
ORACLE_DB1 (ALLEXPERTS) >
ORACLE_DB1 (ALLEXPERTS) > insert into parent_table values (1,'parent 1');

1 row created.

Elapsed: 00:00:00.00
ORACLE_DB1 (ALLEXPERTS) > insert into parent_table values (2,'parent 2');

1 row created.

Elapsed: 00:00:00.01
ORACLE_DB1 (ALLEXPERTS) >
ORACLE_DB1 (ALLEXPERTS) > insert into child_table values (1,1,'child for parent 1');

1 row created.

Elapsed: 00:00:00.00
ORACLE_DB1 (ALLEXPERTS) > insert into child_table values (2,2,'child for parent 2');

1 row created.

Elapsed: 00:00:00.00
ORACLE_DB1 (ALLEXPERTS) > insert into child_table values (1,3,'another child for parent 1');

1 row created.

Elapsed: 00:00:00.00
ORACLE_DB1 (ALLEXPERTS) >
ORACLE_DB1 (ALLEXPERTS) > insert into subchild_table values (1,1,1,'subchild for child 1');

1 row created.

Elapsed: 00:00:00.00
ORACLE_DB1 (ALLEXPERTS) > insert into subchild_table values (2,2,2,'subchild for child 2');

1 row created.

Elapsed: 00:00:00.00
ORACLE_DB1 (ALLEXPERTS) > insert into subchild_table values (1,3,3,'another subchild for child 1');

1 row created.

Elapsed: 00:00:00.00
ORACLE_DB1 (ALLEXPERTS) >
ORACLE_DB1 (ALLEXPERTS) >
ORACLE_DB1 (ALLEXPERTS) >
ORACLE_DB1 (ALLEXPERTS) > select p.parent_id, p.parent_info, c.child_id, c.child_info, s.subchild_id, s.subchild_info
2 from parent_table p
3     , child_table c
4     , subchild_table s
5 where p.parent_id = c.parent_id
6 and c.parent_id = s.parent_id
7 and   c.child_id = s.child_id
8 order by 1, 3, 5;

PARENT_ID PARENT_INFO
---------- ----------------------------------------------------------------
CHILD_ID CHILD_INFO
---------- ----------------------------------------------------------------
SUBCHILD_ID SUBCHILD_INFO
----------- ----------------------------------------------------------------
        1 parent 1
        1 child for parent 1
         1 subchild for child 1

        1 parent 1
        3 another child for parent 1
         3 another subchild for child 1

PARENT_ID PARENT_INFO
---------- ----------------------------------------------------------------
CHILD_ID CHILD_INFO
---------- ----------------------------------------------------------------
SUBCHILD_ID SUBCHILD_INFO
----------- ----------------------------------------------------------------

        2 parent 2
        2 child for parent 2
         2 subchild for child 2


Elapsed: 00:00:00.03
ORACLE_DB1 (ALLEXPERTS) >

*********************
Example Ends
*********************

Hope this helps, and if you shall have additional questions, please don't hestitate to let me know

Cheers
Elliot

Ask a Question