with
output_tab as (select 'P1' as output_id, 100 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P2' as output_id, 200 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P3' as output_id, 300 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P4' as output_id, 400 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P5' as output_id, 500 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual),
input_tab as (select 'C8' as input_id, 'P1' as output_id, 1111 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C9' as input_id, 'P1' as output_id, 2222 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C12' as input_id,'P1' as output_id, 3333 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C5' as input_id, 'P2' as output_id, 4444 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C7' as input_id, 'P2' as output_id, 5555 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P1' as input_id, 'P2' as output_id, 100 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('09/17/2018', 'MM/DD/YYYY') as end_date from dual --ends
union all
select 'C6' as input_id, 'P3' as output_id, 9999 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C10' as input_id, 'P3' as output_id, 1122 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C1' as input_id, 'P4' as output_id, 6666 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C2' as input_id, 'P4' as output_id, 7777 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C4' as input_id, 'P4' as output_id, 8888 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P2' as input_id, 'P4' as output_id, 200 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P4' as input_id, 'P5' as output_id, 400 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P3' as input_id, 'P5' as output_id, 300 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P5' as input_id, 'P1' as output_id, 500 as input_key, to_date('09/18/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual -- starts
)
select distinct ot.output_id, ot.output_key, it.input_key, it.input_id, it.start_date, it.end_date
from output_tab ot inner join input_tab it on (ot.output_id = it.output_id)
start with ot.output_id = 'P1'
connect by it.input_key = prior ot.output_key
order by 1, 2