如何将一个表中的多列与另一个查找表连接？

Question

我不知道如何将一个表中多个列与另一个表进行连接。

例如，我有一个名为“document_statuses”的表：

document_statuses table:
+-----------+-------------+
| status_id | status_name |
+-----------+-------------+
|         1 | RECEIVED    |
|         2 | MISSING     |
|         3 | NOT_NEEDED  |
+-----------+-------------+

现在另一张表中，我正在跟踪多个文档的状态:

filings table:

+-----------+-------------+----------------+----------------+----------------+
| filing_id | filing_name | doc1_status_id | doc2_status_id | doc3_status_id |
+-----------+-------------+----------------+----------------+----------------+
|         1 | John        |              1 |              3 |              2 |
|         2 | Mikaela     |              2 |              3 |              2 |
|         3 | Sam         |              1 |              2 |              1 |
+-----------+-------------+----------------+----------------+----------------+

我应该如何编写一个查询来检索每列的 status_name 并生成以下结果：

+-------------+-------------+-------------+------------+
| Filing Name | Doc1 Status | Doc2 Status | Doc3Status |
+-------------+-------------+-------------+------------+
| John        | RECEIVED    | NOT_NEEDED  | MISSING    |
| Mikaela     | MISSING     | NOT_NEEDED  | MISSING    |
| Sam         | RECEIVED    | MISSING     | RECEIVED   |
+-------------+-------------+-------------+------------+

---

我知道如何在每一行中查找document_statuses的单个字段，但不知道如何查找多个字段。如果documents表中只有一个列与document_statuses相关，那么可以简单地使用JOIN语句：

SELECT filing_name, status_name
FROM documents d
LEFT JOIN document_statuses ds ON d.doc1_status = ds.status_id

但是当我需要多个时，我该怎么做呢？

- Zephyr

Answer 1

您需要多次加入状态表，然后在选择子句中对列进行别名以使其格式符合您的要求。请注意，在联接子句中必须为表设置别名，以便您可以在语句的选择子句中引用这些列。

SELECT filing_name
, ds1.status_name AS 'Doc1 Status'
, ds2.status_name AS 'Doc2 Status'
, ds3.status_name AS 'Doc3 Status'
FROM documents d
LEFT JOIN document_statuses ds1 ON d.doc1_status = ds1.status_id
LEFT JOIN document_statuses ds2 ON d.doc2_status = ds2.status_id
LEFT JOIN document_statuses ds3 ON d.doc3_status = ds3.status_id

- SQLing4ever

Answer 2

你需要使用不同的别名三次连接相同的表格。最初的回答中提到了这点。

    SELECT filing_name, ds.status_name, ds1.status_name, 
    ds2.status_name FROM documents d
   LEFT JOIN document_statuses ds ON d.doc1_status
   LEFT JOIN document_statuses ds1 ON d.doc1_status
   LEFT JOIN document_statuses ds2 ON d.doc1_status

- Milorad

---

谢谢。我刚想尝试一下，因为你发布了你的答案。一切都准备好了！ - Zephyr

Answer 3

你可以做到这个：

select t1.filing_name, t2.status_name as doc1_status, t3.status_name as doc2_status, t4.status_name as doc3_status
from filings_table t1
inner join statuses_table t2 on t1.doc1_status_id = t2.status_id
inner join statuses_table t3 on t1.doc2_status_id = t3.status_id
inner join statuses_table t4 on t1.doc3_status_id = t4.status_id

- Neeraj Agarwal

---

谢谢。我看到你的回答后，突然想尝试一下。已经搞定了！ - Zephyr