SQL-Query for a table with a foreign-key-field that references other foreign-key-fields

Question

I have the following structure of tables in my database:

[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       |

The table "workers" has an foreign-key-field ("combined"). The table "combined" has a field name and a foreign-key-field "helper" which again is a table with two foreign-key-fields.

My question is now, what is the simplest SQL-Query to get the following table:

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

I tried it already with some LEFT-JOINS but did not manage it to get all "clearnames" to the table "workers"

Answer 1

This query would work:

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

I used the AS keyword to set the names to your preferred output.

This was tested locally using the provided structures and data.

It's basically 4 simple left joins, and then instead of selecting the ID's I select the name columns of the foreign tables.

The alias on c.name is quoted because we need to escape the special character -

Answer 2

use following query:

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