MySQL 将一张表连接到另外两张表

Question

我有一个 MySQL 数据库，包括以下表格，用于维护一些文档的事务。

tbl_documents 表

+----+---------+------+---------+
| id | file_no | name | subject |
+----+---------+------+---------+
|  1 | A/10    | F1   | a       |
|  2 | A/11    | F2   | b       |
|  3 | A/12    | F3   | c       |
|  4 | A/13    | F4   | d       |
+----+---------+------+---------+ 

tbl_请求

+----+-------------+----------------+---------------+
| id | document_id | requested_date | approved_date |
+----+-------------+----------------+---------------+
|  1 |           1 | 2019-12-01     | 2019-12-02    |
|  2 |           2 | 2019-12-08     | 2019-12-08    |
+----+-------------+----------------+---------------+

tbl_问题

+----+-------------+------------+
| id | document_id | issue_date |
+----+-------------+------------+
|  1 |           1 | 2019-12-05 |
|  2 |           2 | 2019-12-10 |
+----+-------------+------------+

我想通过连接上面三个表来获得以下/所需的输出。

期望的输出

+---------+------+---------+----------------+---------------+------------+
| file_no | name | subject | requested_date | approved_date | issue_date |
+---------+------+---------+----------------+---------------+------------+
| A/10    | F1   | a       | 2019-12-01     | 2019-12-02    | 2019-12-05 |
| A/11    | F2   | b       | 2019-12-08     | 2019-12-08    | 2019-12-10 |
+---------+------+---------+----------------+---------------+------------+

为此，我使用了以下查询

select tbl_documents.file_no, tbl_documents.name, tbl_documents.subject, requested_date, approved_date, tbl_issues.issue_date 
from tbl_documents            
right join tbl_requests on tbl_requests.document_id=tbl_documents.id
right join tbl_issues on tbl_issues.document_id=tbl_documents.id

但没有得到预期的输出。有人可以帮忙吗？

Answer 1

只需使用内部联接，如下所示:

select 
  d.file_no,
  d.name,
  d.subject,
  r.requested_date,
  r.approved_date,
  i.issue_date
from tbl_documents d
join tbl_requests r on r.document_id = d.id
join tbl_issues i on i.document_id = d.id