I am struggling to articulate in a search query what I'm trying to do so I'm sorry if this has been answered before. I'm hoping that by posting a full example someone will be able to point me in the right direction. I'm looking to either figure out a mySQL query, or learn a better way to structure my database, to make the following possible.

If I have 4 tables, what is the best way to link through a table to another. Below I have a site\_table that has an ID for a manager (General Manager of the site), a region and a country. The region\_table and country\_table also point at the manager\_table. The manager\_id on the site\_table also points at the manager\_table. Here are what the tables may look like:

```
site_table
+------------+------------+-----------+------------+
|    name    | manager_id | region_id | country_id |
+------------+------------+-----------+------------+
| Manchester |          1 |         1 |          1 |
| Essex      |          2 |         2 |          1 |
| Perth      |          3 |         3 |          2 |
| Birmingham |          4 |         1 |          1 |
+------------+------------+-----------+------------+

region_table
+-----------+---------+------------+
| region_id |  name   | manager_id |
+-----------+---------+------------+
|         1 | Central |          5 |
|         2 | South   |          6 |
|         3 | North   |          7 |
+-----------+---------+------------+

country_table
+------------+----------+------------+
| country_id |   name   | manager_id |
+------------+----------+------------+
|          1 | England  |          9 |
|          2 | Scotland |          8 |
+------------+----------+------------+

manager_table
+------------+-----------------+
| manager_id |      name       |
+------------+-----------------+
|          1 | Joe Bloggs      |
|          2 | Graham Smith    |
|          3 | Pat Sharp       |
|          4 | Sally May       |
|          5 | Peter Barratt   |
|          6 | Jimmy Stone     |
|          7 | Kim Keller      |
|          8 | Peter Sheet     |
|          9 | Matthew Lampart |
+------------+-----------------+

```

I would like to query the database to return the site name, General Manager's (GM) name, Regional Manager's (RM) name and Country Manager's (CM) name like so:

```
+------------+--------------+---------------+-----------------+
| site_name  |   GM_name    |    RM_name    |     CM_name     |
+------------+--------------+---------------+-----------------+
| Manchester | Joe Bloggs   | Peter Barratt | Matthew Lampart |
| Essex      | Graham Smith | Jimmy Stone   | Matthew Lampart |
| Perth      | Pat Sharp    | Kim Keller    | Peter Sheet     |
| Birmingham | Sally May    | Peter Barratt | Matthew Lampart |
+------------+--------------+---------------+-----------------+

```

I feel like to do this I would have to link the site\_table to the region\_table and country\_table, and then link the manager\_table to the site\_table, region\_table and country\_table but I'm tying myself up in knots trying to do it. I see the links looking like this:

```
site_table---+-----------------------+
             |                       |
             +-----region_table----manager_table
             |                       |
             +----country_table------+

```

If anyone can give any advice on the best way to write this query, or a better way to structure the database, that would be really helpful.