Find hierarchy in two tables

Question

I have two tables (oracle) parent and child.

cols in parent (Id, type, name)
cols in child (child_id, parent_id)

Some context: We have this hierarchy in our application. type = A -> B -> C ->D .. A can contain multiple B's and each of those B's can contain multiple C's and so on.

Requirement

if I have an id (lets say "1")

I need to search the child table on the column parent_id. This will return me multiple rows. I need to take the child_id of these rows and search back in the parent table on column (Id). Then take the details (Id, type, name) from the parent table. (this is required in the output) Please note, we will only take the Ids where the type matches the hierarchy explained above.

Then we search these Id back in child table and repeat this process. till we reach the final hierarchy of D..

Remember, the id passed can be of either A or B or C or D.. So we have to intelligently search the ids for the types under it in the hierarchy.

Need some help with the SQL for this.

Eg.

table parent
-----------
(1,"A","parent1")
(2,"B","parent2")
(3,"C","parent3")
(4,"D","parent4")

table child
------------
(2,1)
(88,1)
(98,1)
(100,2)
(3,2)
(4,2)

output
--------
parent,child
--------------
(1,2)
(2,3)
-- each of these can have multiple children for eg
(1,66)
(1,67)
(2,90)
and so on.. 

Answer 1

You don't actually need your parent table for this query at all until you want to display details for the rows.

There's 2 basic ways to do hierarchical queries in Oracle - the old-school proprietary connect by, and the ANSI-standard recursive CTE. You can find lots of similar questions on here if you search for those terms. Oracle also has this helpful tutorial

Connect by example:

select parent_id, child_id
from child
start with parent_id = 1
connect by prior parent_id = child_id

Recursive CTE:

with cte(child_id, parent_id) as (
    -- anchor (similar to "start with")
    select child_id, parent_id
    from child
    where parent_id = 1
    union all
    -- recursion (similar to "connect by")
    select child_id, parent_id
    from child
    join cte
    on cte.child_id = child.parent_id
    )
select * from cte;