How to make two joins between two tables in MySQL such that they are interlinked to each other?

Question

I have two tables in a company database named employee and branch with a single foreign key each. The employee table looks like this:

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| emp_id     | int         | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(30) | YES  |     | NULL    |                |
| last_name  | varchar(30) | YES  |     | NULL    |                |
| birth_date | date        | YES  |     | NULL    |                |
| sex        | varchar(1)  | YES  |     | NULL    |                |
| salary     | int         | YES  |     | NULL    |                |
| super_id   | int         | YES  | MUL | NULL    |                |
| branch_id  | int         | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

And the branch table looks like this:

+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| branch_id      | int         | NO   | PRI | NULL    |       |
| branch_name    | varchar(30) | YES  |     | NULL    |       |
| mgr_id         | int         | YES  | MUL | NULL    |       |
| mgr_start_date | date        | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

In the employee table, the branch_id foreign key references the branch_id of the branch table. In the branch table, the manager_id foreign key references the employee_id of the employee table.

I would form two joins between these two tables in circular fashion(saying informally) such that employee.branch_id forms a join with branch.branch_id, and such that branch.manager_id forms a join with employee.employee_id.

So what I want the query with these two joins to return is:

employee.first_name AS employee_name, employee.branch_id, branch.branch_name, branch.manager_id, employee.employee_id AS manager_name

I couldn't think of a possible solution to this issue as the LEFT table in each of the joins is different and I don't know how to define multiple LEFT tables for each join in a single SQL query.

DBMS: MySQL v8.0.26

P.S: My question is different from this question as in the aforementioned question, the LEFT table for both of the joins is the same and in my case, it is not.

Answer 1

How to proceed towards the solution

Two joins can be formed between two columns by using Table aliases. As the question specifies, that one join is to be formed between the employee and the branch table, and another join needs to be formed between the branch and the employee table. The little bit tricky part of these types of joins is the relation specified after the ON keyword that joins the two tables.

As @philipxy writes in a comment to this question:

Constraints (including FKs & PKs) need not hold, be declared or be known in order to record or query. Joins are binary, the left table is the result of any previous joins in a series without parentheses. Except for output column order, inner & cross joins have no direction, t join u on c is u join t on c.

So according to the comment, we would form a join between employee and branch and another join between employee and an alias of branch table called branch2. The common confusion here is that most people(including me earlier) think that there is a "direction" of joins, the thing that philipxy covers in his aforementioned comment.

The solution to the problem

You can write a SQL query which queries the first_name, last_name and branch_id from the employee table and the branch_name from the branch table and forms a join between the two tables on the basis of branch_id. You have to query the mgr_id from the alias of the branch table called branch2; you have to query the first_name and the last_name of the branch managers from the employee table. You can easily join the employee and the branch table on the basis of emp_id such that the mgr_id=emp_id.

You can finally write the SQL query for the problem like this:

SELECT employee.first_name, employee.last_name, employee.branch_id,
branch.branch_name,
branch2.mgr_id, employee.first_name AS manager_first_name, employee.last_name AS manager_last_name
FROM employee
JOIN branch ON employee.branch_id=branch.branch_id
JOIN branch branch2 ON branch2.mgr_id=employee.emp_id;

Extra information

The above mentioned query would return this:

+------------+-----------+-----------+-------------+--------+--------------------+-------------------+
| first_name | last_name | branch_id | branch_name | mgr_id | manager_first_name | manager_last_name |
+------------+-----------+-----------+-------------+--------+--------------------+-------------------+
| David      | Wallace   |         1 | Corporate   |    100 | David              | Wallace           |
| Michael    | Scott     |         2 | Scranton    |    102 | Michael            | Scott             |
| Josh       | Porter    |         3 | Stamford    |    106 | Josh               | Porter            |
+------------+-----------+-----------+-------------+--------+--------------------+-------------------+

These results might look useless as we have formed an INNER JOIN between the tables so it just returns us the name of the employees who are "managers" of a specific branch. If you form a LEFT JOIN between the tables instead of an INNER JOIN you would get results like this:

+------------+-----------+-----------+-------------+--------+--------------------+-------------------+
| first_name | last_name | branch_id | branch_name | mgr_id | manager_first_name | manager_last_name |
+------------+-----------+-----------+-------------+--------+--------------------+-------------------+
| David      | Wallace   |         1 | Corporate   |    100 | David              | Wallace           |
| Jan        | Levinson  |         1 | Corporate   |   NULL | Jan                | Levinson          |
| Michael    | Scott     |         2 | Scranton    |    102 | Michael            | Scott             |
| Angela     | Martin    |         2 | Scranton    |   NULL | Angela             | Martin            |
| Kelly      | Kapoor    |         2 | Scranton    |   NULL | Kelly              | Kapoor            |
| Stanley    | Hudson    |         2 | Scranton    |   NULL | Stanley            | Hudson            |
| Josh       | Porter    |         3 | Stamford    |    106 | Josh               | Porter            |
| Andy       | Bernard   |         3 | Stamford    |   NULL | Andy               | Bernard           |
| Jim        | Halpert   |         3 | Stamford    |   NULL | Jim                | Halpert           |
+------------+-----------+-----------+-------------+--------+--------------------+-------------------+

These results were not as expected as the employees who are not managers of any branch just have a mgr_id with NULL value whereas the branch that they word in actually has a manager. With the mgr_id being NULL, the manager_first_name and manager_last_name have unexpected results too.

The above occurs because we cannot have the same manager for two employees because mgr_id can not be the same accross rows as it is the emp_id which is the PRIMARY KEY of the employee table.

Credits

• @philpxy 's comments on this question