Oracle: fetch value of columns based on another table column-CodePudding

I have two tables, one of them is a main table and the other is the decode. The decode has 142 rows and 2 columns. Cause of death code and description. The main table has person information and the reason of death and secondary reason of death.

Decode table

code	description
1	infection
2	covid

main table

client	reason_1	reason_2
01	1	null
02	2	1
03	1	2

Expected table

client	reason_1	reason_2	reason_1_desc	reason_2_desc
01	1	null	infection	null
02	2	1	covid	infection
03	1	2	infection	covid

How can I get this output without typing in all the values from decode table and using decode function or subquery factoring?

CodePudding user response：

Join the description table twice (for each of two reasons):

Sample data:

SQL> with
  2  t_decode (code, description) as
  3    (select 1, 'infection' from dual union all
  4     select 2, 'covid'     from dual
  5    ),
  6  t_main (client, reason, reason_2) as
  7    (select '01', 1, null from dual union all
  8     select '02', 2, 1    from dual union all
  9     select '03', 1, 2    from dual
 10    )

Query:

 11  select m.client, m.reason, m.reason_2,
 12    d1.description reason_desc,
 13    d2.description reason_2_desc
 14  from t_main m left join t_decode d1 on d1.code = m.reason
 15                left join t_decode d2 on d2.code = m.reason_2
 16  order by m.client;

CL     REASON   REASON_2 REASON_DE REASON_2_
-- ---------- ---------- --------- ---------
01          1            infection
02          2          1 covid     infection
03          1          2 infection covid

SQL>