Join with same table with three different conditions

I have a requirement to join with same table with three different conditions. Below are the scenarios:

Table1 :

ID,NAME,INSERT_DT

Table2 :

ID_FK,DESC,REC_BEG_DT,REC_END_DT,REC_INDC,SYSTEM_CODE,SERVICE_SRT_DT,SERVICE_END_DT

Joining Conditions:

1.

Table1.ID=Table2.ID_FK
SYSTEM_CODE='ABC'
REC_INDC='A'
Table1.INSERT_DT >= Table2.SERVICE_SRT_DT AND Table1.INSERT_DT <= Table2.SERVICE_END_DT

If no results return use join condition 2.
If the above condition return multiple results then pick the one that satisfy

Table1.INSERT_DT >= Table2.REC_BEG_DT AND Table1.INSERT_DT <= Table2.REC_END_DT

2. Table1.ID=Table2.ID_FK SYSTEM_CODE=’ABC’ REC_INDC=’A’

   If no results return use join condition 3.
   If the above condition return multiple results then pick the one that satisfy

   Table1.INSERT_DT >= Table2.REC_BEG_DT AND Table1.INSERT_DT <= Table2.REC_END_DT

3. Table1.ID=Table2.ID_FK SYSTEM_CODE=’ABC’

   If no results return Null.
   If the above condition return multiple results then pick the one that satisfy

   Table1.INSERT_DT >= Table2.REC_BEG_DT AND Table1.INSERT_DT <= Table2.REC_END_DT

Adding sample input and output

Table1

Table2

Edit: Added one more scenario

Tab1

|ID|NAME|INSERT_DT|

|1|XY|29/04/2021|

Tab2

ID_FK|DESC|REC_BEG_DT|REC_END_DT|REC_INDC|SYSTEM_CODE|SERVICE_SRT_DT|SERVICE_END

1|XY DESC1|1/4/2021|30/04/2021|I|ABC|1/3/2021|30/03/2021

1|XY DESC2|1/3/2021|30/03/2021|I|ABC|1/4/2021|30/04/2021

1|XY DESC2|1/3/2021|30/03/2021|I|ABC|1/3/2021|30/03/2021

Logically 1st record from table should be selected.

Thank you

Answer

You can flag rows with optional conditions priority flags and select a row with the highest priority. A query template

select  ...
from
( 
    select  ... , 
        -- priority flags of optional conditions 
       row_number() over(partition by Table1_id order by 
        case when REC_INDC='A' then '0' else '9' end
        || case when Table1.INSERT_DT >= Table2.SERVICE_SRT_DT AND Table1.INSERT_DT <= Table2.SERVICE_END_DT then '0' else '9' end
        || case when Table1.INSERT_DT >= Table2.REC_BEG_DT AND Table1.INSERT_DT <= Table2.REC_END_DT then '0' else '9' end ) rn
    from ...
    where ...
         -- mandatory condition
        Table1.ID=Table2.ID_FK
        AND SYSTEM_CODE='ABC'
) t
where rn = 1

EDIT

To take all REC_INDC =’I’ case into account

select *
from ( 
    select table1.INSERT_DT, table2.* , 
       f.flags,
       row_number() over(partition by Table1.id order by f.flags) rn
    from table1
    join Table2 on
         -- mandatory condition
        Table1.ID=Table2.ID_FK
        AND SYSTEM_CODE='ABC'
    cross join lateral(
        -- priority flags of optional conditions 
         select case when  REC_INDC='A' and Table1.INSERT_DT >= Table2.SERVICE_SRT_DT AND Table1.INSERT_DT <= Table2.SERVICE_END_DT then '00' 
                 else 
                    case when REC_INDC='A' then '90' else '99' end end
                 || case when Table1.INSERT_DT >= Table2.REC_BEG_DT AND Table1.INSERT_DT <= Table2.REC_END_DT then '0' else '9' end  flags
         from dual ) f 
     ) t
where rn = 1

Lateral is here just as a convinience, the exprssion can be easyly merged into SELECT list.

db<>fiddle