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.
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
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
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
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)
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.
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) )
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)
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;