SQL-查询具有引用其他外键字段的外键字段的表

Question

我的数据库中有以下表的结构：

[table workers]
ID [PK] | worker | combined [FK]
--------+--------+--------------+
1       | John   |    2
--------------------------------+
2       | Adam   |    1

[table combined]
ID [PK] | name   | helper [FK]
--------+----------------------+
1       | name1  |  1
2       | name2  |  2

[table helper]
ID [PK] | department [FK] | location [FK]
--------+-------------+-------------------
1       |       2         |     3  
2       |       1         |     1

[table departments]
ID [PK] | department
--------+-------------+
1       | Development |
2       | Production  |

[table location]
ID [PK] | department
--------+--------------+
1       | Paris        |
2       | London       |
3       | Berlin       |

表“worker”有一个外键字段(“组合”)。表“组合”有一个字段名和一个外键字段"helper“，这也是一个包含两个外键字段的表。

我的问题是，获得下表的最简单SQL查询是什么：

[table workers]
ID [PK] | worker | combined-Name| department | location
--------+--------+--------------+------------+-----------
1       | John   |    name2     | Development|   Paris
--------------------------------+------------+-----------
2       | Adam   |    name1     | Production |   Berlin

我已经尝试了一些左连接，但没有设法将所有的“清晰名称”都放到表“worker”中。

Answer 1

下面的查询将起作用：

SELECT w.ID, worker, c.name AS `combined-Name`, d.department, l.department as 
location FROM workers w
LEFT JOIN combined c ON c.ID = w.combined
LEFT JOIN helper h ON h.ID = c.helper
LEFT JOIN departments d ON d.ID = h.department
LEFT JOIN location l ON l.ID = h.location
GROUP BY w.ID

我使用了AS关键字来设置首选输出的名称。

这是使用提供的结构和数据在本地进行测试的。

这基本上是4个简单的左连接，然后不是选择ID，而是选择外部表的名称列。

因为我们需要转义特殊字符-，所以c.name上的别名是用引号引起的

Answer 2

使用以下查询：

select [workers].worker,[combined].name as combined-name,[departments].name as department,[location].name as location  from [workers]
left join [combined] on [workers].combined = [combined].combined
left join [helper] on [helper].ID = [combined].helper
left join [departments] on [departments].ID = [helper].department
left join [location] on [location].ID = [helper].location