with regions as (
select 3 r, 'Московская' region, 'Чеховский' subregion, 'Чехов' city from dual union all
select 2 r, 'Московская' region, 'Чеховский' subregion, null city from dual union all
select 1 r, 'Московская' region, null subregion, null city from dual union all
select 6 r, 'Ленинградская' region, 'Выборгский' subregion, 'Выборг' city from dual union all
select 5 r, 'Ленинградская' region, 'Выборгский' subregion, null city from dual union all
select 4 r, 'Ленинградская' region, null subregion, null city from dual 
),
dat as (
select 1 r,1 s,1 c, 1 result_needed from dual union all
select 1 r,1 s,2 c, 2 result_needed from dual union all
select 1 r,2 s,2 c, 2 result_needed  from dual union all
select 2 r,2 s,2 c, 2 result_needed  from dual union all
select 1 r,2 s,3 c, 3 result_needed  from dual union all
select 4 r,4 s,4 c, 4 result_needed  from dual union all
select 4 r,5 s,6 c, 6 result_needed  from dual union all
select 4 r,5 s,5 c, 5 result_needed  from dual union all
select 5 r,5 s,6 c, 6 result_needed  from dual union all
select 3 r,4 s,6 c, 3 result_needed  from dual --здесь подходят под условие и id=3, и 6 - выбираем первый самый длинный адрес по порядку следования полей (r,s,c)
)
select *
from dat
inner join regions r1 on dat.r=r1.r
inner join regions r2 on dat.s=r2.r
inner join regions r3 on dat.c=r3.r

with regions as (--справочник
select 30 r, 'Московская' region, 'Чеховский' subregion, 'Чехов' city from dual union all select 20 r, 'Московская' region, 'Чеховский' subregion, null city from dual union all select 10 r, 'Московская' region, null subregion, null city from dual union all select 60 r, 'Ленинградская' region, 'Выборгский' subregion, 'Выборг' city from dual union all select 50 r, 'Ленинградская' region, 'Выборгский' subregion, null city from dual union all select 40 r, 'Ленинградская' region, null subregion, null city from dual union all select 70 r, null region, 'sss' subregion, null city from dual ), 
regions_q as 
( select regions.*, --вес (качество) заполнения справочника 
case when region is not null and subregion is not null and city is not null then 3
     when region is not null and subregion is not null and city is     null then 2
     when region is not null and subregion is     null and city is     null then 1
     else 0
end as reg_quality     
from regions)
,dat as (--факты
select 10 r,10 s,10 c, 10 result_needed from dual union all select 10 r,10 s,20 c, 20 result_needed from dual union all select 10 r,20 s,20 c, 20 result_needed  from dual union all select 20 r,20 s,20 c, 20 result_needed  from dual union all select 10 r,20 s,30 c, 30 result_needed  from dual union all select 40 r,40 s,40 c, 40 result_needed  from dual union all select 40 r,50 s,60 c, 60 result_needed  from dual union all select 40 r,50 s,50 c, 50 result_needed  from dual union all select 50 r,50 s,60 c, 60 result_needed  from dual union all select 30 r,40 s,60 c, 30 result_needed  from dual --здесь подходят под условие и id=30, и 60 - выбираем первый самый длинный адрес по порядку следования полей (r,s,c)
)
, dat2 as (
select dat.*,
r1.r id1, r1.region region1, r1.subregion subregion1, r1.city city1, r1.reg_quality reg_quality1, r2.r id2,r2.region region2, r2.subregion subregion2, r2.city city2, r2.reg_quality reg_quality2, r3.r id3,r3.region region3, r3.subregion subregion3, r3.city city3, r3.reg_quality reg_quality3,
 --наилучший заполненный из трех 
greatest(r1.reg_quality, r2.reg_quality, r3.reg_quality) best_quality 
from dat inner join regions_q r1 on dat.r=r1.r inner join regions_q r2 on dat.s=r2.r inner join regions_q r3 on dat.c=r3.r
)
select dat2.* ,
--вернуть id справочника с best_quality
case when best_quality = reg_quality1 then id1
     when best_quality = reg_quality2 then id2
     when best_quality = reg_quality3 then id3 end as best_id from dat2