Join table with values in multiple columns to table with values in multiple rows-CodePudding

I have two tables.

REJECT_REASONS

Account_No	Resn_Id1	Resn_Id2	Resn_Id3	Resn_Id4
100	0001	0005	0006	0104
101	0005	0006	0104
102	0001	0006
103
104	0001

REASON_DESC

Resn_Id	Resn_Desc
0001	Bad Account
0005	Duplicate Account
0006	Invalid Data
0104	Invalid address

I want to join the table REJECT_REASONS with REASON_DESC on each of the columns resn_id1, resn_id2, resn_id3 and resn_id4 with resn_id and get resn_desc in the result.

I tried the below query:

select r.account_no,r.resn_id1,rd1.resn_desc,
r.resn_id2,rd2.resn_desc,
r.resn_id3,rd3.resn_desc,
r.resn_id4,rd4.resn_desc
from reject_reasons r, reason_desc rd1, reason_desc rd2, reason_desc rd3, reason_desc rd4
where r.resn_id1=rd1.resn_id( )
and  r.resn_id2=rd2.resn_id( )
and r.resn_id3=rd3.resn_id( )
and r.resn_id4=rd4.resn_id( )
;

Please let me know if there is a way to simplify the query.

CodePudding user response：

I guess you want something like this

with reject_reasons ( Account_No , Resn_Id1 , Resn_Id2 , Resn_Id3 , Resn_Id4 ) 
as 
(
select 100, '0001' , '0005' , '0006' , '0104' from dual union all
select 101, '0005' , '0006' , '0104' ,  null  from dual union all
select 102, '0001' , '0006' ,  null  ,  null  from dual union all
select 103,  null  ,  null  ,  null  ,  null  from dual union all
select 104, '0001' ,  null  ,  null  ,  null  from dual 
), 
reason_desc ( Resn_Id , Resn_Desc ) 
as 
(
select  '0001' , 'Bad Account'        from dual union all 
select  '0005' , 'Duplicate Account'  from dual union all 
select  '0006' , 'Invalid Data'       from dual union all 
select  '0104' , 'Invalid address'    from dual 
) 
select r.account_no,r.resn_id1,rd1.resn_desc,
r.resn_id2,rd2.resn_desc,
r.resn_id3,rd3.resn_desc,
r.resn_id4,rd4.resn_desc
from reject_reasons r 
   left join reason_desc rd1 on r.resn_id1=rd1.resn_id 
   left join reason_desc rd2 on r.resn_id2=rd2.resn_id
   left join reason_desc rd3 on r.resn_id3=rd3.resn_id 
   left join reason_desc rd4 on r.resn_id4=rd4.resn_id
order by 1 ;

A demo here

db<>fiddle

CodePudding user response：

Since there is no desired output, I thought another approach could be concatenating the descriptions.

WITH REJECTION AS (SELECT DISTINCT *
    FROM(
    SELECT
    Account_No,
    Resn_Id1 AS Resn_Id
    FROM reject_reasons r
        UNION
    SELECT 
    Account_No,
    Resn_Id2 AS Resn_Id
    FROM reject_reasons r
        UNION
    SELECT 
    Account_No,
    Resn_Id3 AS Resn_Id
    FROM reject_reasons r
        UNION
    SELECT 
    Account_No,
    Resn_Id4 AS Resn_Id
    FROM reject_reasons r)) 

SELECT REJECTION.Account_No,
listagg(rdesc.Resn_Desc, ', ') WITHIN GROUP (ORDER BY rdesc.Resn_Desc)
FROM REJECTION  
LEFT JOIN reason_desc rdesc
ON rdesc.Resn_Id = REJECTION.Resn_Id
GROUP BY REJECTION.Account_No