SQL & PL/SQL

This is a follow up to this question:

reverse connect by?

My Database is

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,

Real Application Testing and Unified Auditing options

I have a base_data table with a self join that represents hierarchical data (master/detail) like this:

select 1000*level+1 as id
      ,case when mod(level,5) = 0 then 'parent_' end || 'name_'||level as name
      , 1000* (5*ceil(level/5))+1 as ref_id
      , case when mod(level,5) > 0 then mod(level,5) end as order_nr
      from dual connect by level < 21

returns:

additionally I have a refering_data table that refers the data above:

 select 10000*level+2 as referrer_id
      , 'referrer_name_'||level as referrer_name
      , 1000* (6*level)-1999 as fk_id
      from dual connect by level < 4

returns:                                                          

The other answer helped me to find the parent for a given ID in my base data.

What I need now is: how do I combine the base data and the referencing data so that for each entry in my referring_data table I get the corresponding entry from the base_data table and (if exists) it's parent.

Duplicate parents should be removed and the details should be ordered behind their parent in he order defined by the ORDER_NR column.

The desired output is:

I tried to adopt the other answer by  doing this:

with base_data as(
select 1000*level+1 as id
      ,case when mod(level,5) = 0 then 'parent_' end || 'name_'||level as name
      , 1000* (5*ceil(level/5))+1 as ref_id
      , case when mod(level,5) > 0 then mod(level,5) end as order_nr
      from dual connect by level < 21
), referring_data as(
 select 10000*level+2 as referrer_id
      , 'referrer_name_'||level as referrer_name
      , 1000* (3*level)-1999 as fk_id
      from dual connect by level < 6
) 
select     level, bd.*
from       base_data bd
start with id  in (select fk_id from referring_data)
connect by id = prior nullif(ref_id,id)
order siblings by order_nr
;

but that gives a "parent" row for each "detail row":

How do I group the result by parent_rows keeping the order of the detail rows?

Also the Parents need to be ordered by their ID.

Message was edited by: TPD-Opitz added example for referring data