Your Location is: Home > Sql

Query to get child records of given parent id

From: Bangkok View: 4052 Anand 

Question

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

Best answer

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.

Another answer

If you just want the immediate children, then an exists subquery is sufficient:

select t2.*
from table2 t2
where exists (
    select 1 from table1 t1 where t1.child_id = t2.id and 5 in (t1.child_id, t1.parent_id)
)

Or:

select t2.*
from table2 t2
where t2.id = 5 or exists (
    select 1 from table1 t1 where t1.child_id = t2.id and t1.parent_id = 5
)

If, on the other hand, you want all children, regardless of their level, then I would recommend a recursive query:

with cte (child_id, parent_id) as (
    select child_id, parent_id from table1 where child_id = 5
    union all
    select t1.child_id, t1.parent_id
    from cte c
    inner join table1 t1 on t1.parent_id = c.child_id
)
select t2.*
from table2 t2
where exists (select 1 from cte c where c.child_id = t2.id)

Another answer

You can use a query like this one to find the proper children. Just change the ID you are searching for in the CONNECT_BY_ROOT (t1.id) = 5 clause from 5 to any id and it should work as expected.

    SELECT t2.name, t2.age, t1.id
      FROM table1 t1, table2 t2
     WHERE t1.id = t2.id AND CONNECT_BY_ROOT (t1.id) = 5
CONNECT BY PRIOR t1.id = t1.parent_id;