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

Question

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.

Use standard JOIN syntax to make the query readable. You need 4 joins anyway. — Serg, Oct 03 '21 at 08:40
[`and`](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Logical-Conditions.html#GUID-C5E48AF2-3FF9-401D-A104-CDB5FC19E65F): *Returns `TRUE` if **both** component conditions are `TRUE`. Returns `FALSE` if either is `FALSE`. Otherwise returns `UNKNOWN`.* If not all the values in the row are the same, it will obviously not join anything — astentx, Oct 03 '21 at 10:25
The join syntax you are using has been replaced by standard joins (`LEFT OUTER JOIN`) twenty years ago in Oracle - and Oracle was very late to do so at that; explicit joins where introduced in the SQL standard in 1992. Hence it feels kinda weird when you are saying this is a query you tried *now*. Your query is correct, but you should not use this ancient join syntax anymore. Replace it with standard joins. — Thorsten Kettner, Oct 04 '21 at 06:17
@ThorstenKettner Thanks for letting me know. I have been using these as they are still supported in 19c. Anyway, I will replace it with LEFT OUTER JOIN. My main question is "If there is a way to write the query without joining on the same table 4 times". — marecar, Oct 04 '21 at 14:05

score 0 · Answer 1 · answered Oct 03 '21 at 09:01

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

Larx · Answer 2 · 2021-10-03T09:22:13.677

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

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

2 Answers2