Query to get child records of given parent id

I have a query to find child data from Table2 which having hierarchical data which is defined in other table i.e. TABLE1 in oracle.

TABLE1

ID, CHILD_ID, PARENT_ID
1,  1           
2,  2,      1   
3,  3,      2   
4,  4,      3   
5,  5,      4   
6,  6,      4   
7,  7,      4   
8,  8,      5   
9,  9,      5

TABLE2

NAME,AGE,ID
JJ,22,1
XX,19,2
YY,20,3
KK,21,4
PP,18,5
CC,19,6
DD,22,7
SS,44,8
QQ,33,9

When i query for ID 7 the output should

NAME,AGE,ID
DD,22,7

As there is no child of 7

When i query 5 then it should show below as 8 & 9 are child of 5

NAME,AGE,ID
PP,18,5
SS,44,8
QQ,33,9

Please suggest, Thanks in Advance

Solution

You could do something like the following to handle the general case (i.e. will get not just parents and children, but potentially children-of-children and so on).

with thevalues as
(
 SELECT child, parent
 FROM table1 
 START WITH parent=4
 CONNECT BY PRIOR child = parent
)
SELECT *
FROM table2
WHERE id IN (SELECT child FROM thevalues UNION ALL SELECT parent FROM thevalues)

where parent=4 defines the starting record. Connect By is used for these hierarchical queries like these.

Although the above also works for the simple case in your example, if you don't care about children-of-children, you might prefer something like

SELECT *
FROM table2
WHERE id=4
UNION ALL
SELECT *
FROM table2
WHERE id IN
(
 SELECT child
 FROM table1
 WHERE parent=4
)

Note that I've hardcoded 4 in two places in this example.