mysql - 连接在 THIRD 表上包含空值的表

Question

我正在研究电子词典，我有 2 个表:

第一个表:Term

+----+--------------+| id | term_text    |+----+--------------+| 1  | hello        || 2  | how are you? || 3  | hola         |+----+--------------+

Second table: Dictionary

<pre>
+-----------+------------+
| term_id_1 | term_id_2  |
+-----------+------------+
| 1         | 3          | /* hello - hola
| 2         | NULL       | /* how are you? - NULL
+-----------+------------+
</pre>

我想这样显示:

<pre>


+--------------+-------------+
| term_text_1  | term_text_2 |
+--------------+-------------+
| hello        | hola        | 
| how are you? | NULL        | 
+--------------+-------------+

</pre>

我尝试使用以下查询获取数据:

<pre>
SELECT t1.term_text_1, t2.term_text_2
FROM dictionary d, term t1, term t2
WHERE d.term_id_1 = t1.id
AND d.term_id_2 = t2.id
</pre>

但我只得到值不为空的地方!

Answer 1

你需要加入 dictionary 和 term 两次:

select 
  t1.term_text term_text_1,
  t2.term_text term_text_2
from dictionary 
left join term t1 on t1.id = term_id_1 
left join term t2 on t2.id = term_id_2 

参见 demo .
结果:

| term_text_1  | term_text_2 |
| ------------ | ----------- |
| hello        | hola        |
| how are you? |             |