Oracle:根据另一个表列获取列的值

Question

我有两个表，其中一个是主表，另一个是解码。解码有142行和2列。死因代码和描述。主表有人员信息、死亡原因和次要死亡原因。

<<p>解码表/strong>

代码tbody> <<tr>2

描述
1	感染
	covid

连接description表两次(有两个原因):

样本数据:

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    )

查询:

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>