Quote: refer to 1st floor nayi_224 response:

 with tab1 as (
Select the 'a' target_id from dual union all 
Select the 'd' tar from dual 
) 
Tab2 as (
Select the 'a' id, null parent_id from dual union all 
Select 'b' id, 'a' parent_id from dual union all 
Select the 'c' id, 'b' parent_id from dual union all 
Select the 'd' id, null parent_id from dual union all 
Select the 'e' id, 'd' parent_id from dual union all 
Select the 'f' id, 'b' parent_id from dual 
) 
, tab3 as (
The select connect_by_root (t1. Id) id, 
Case the when t1. Id in (select v1. Target_id from tab1 v1) then t1. Id else null end tar_ 
The from tab2 t1 
Connect by the prior t1. The parent_id=t1. Id 
And the prior t1. Id not in (select v1. Target_id from tab1 v1) 
) 
Select a t1. Id, Max (t1) tar_) from tab3 t1 group by t1. Id 
The order by t1. Id 
; 

Oh oh, sorry, may I wasn't as detailed examples, this kind of writing is when all of the target groups are the top of the organization, I faced the question now is, also may be appear the following situation:
Known:
Table 1
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Target range departments
A, B
C C
E E

Table 2
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
The superior departments
A
A, B
C B
D
E D
F B


O: according to the department to find the corresponding target departments, if you cannot find, is to find the corresponding target departments at the next higher level, circulation, until find the target department
Finally realizes the effect is as follows:

Target departments
-- -- -- -- -- -- -- -- -- --
A
A, B
C C
D
E E
F

Can help to have a look?

 with tab1 as (
Select the 'a' target_id from dual union all 
Select the 'c' target_id from dual union all 
Select the 'e' tar from dual 
) 
Tab2 as (
Select the 'a' id, null parent_id from dual union all 
Select 'b' id, 'a' parent_id from dual union all 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnull