实施3个表连接

Question

我有三张桌子

employees     
-------------   
|idEmployee |  
|   name    |  

user-employee   
----------------                           
|  idUser      |-> MongoID      
|  idEmployee  |-> FK employees.idEmployee    

employees-supervisor
--------------------
|    idEmployee    |-> FK employees.idEmployee          
|    idUser        |-> FK user-employee.idUser    

我想要下一张桌子

idEmployee | name   | idUser | nameSupervisor
    1      | John   |     A2 |     Jane

Employee表包含公司的所有员工，user-employee包含系统用户的所有员工，user-employee.idUser是mongo文档的用户，employees-supervisor包含一个列表，该列表将idUser (mongoid)与员工的id (mysql)进行匹配。

我想要的是显示所有员工及其各自的主管(如果有的话)，现在我得到了idUser

SELECT employees.*, e1.idUser as idSupervisor, e2.name as supervisor
  FROM employees
  left JOIN user-employee e1 ON  e1.idEmployee = employees.idEmployee 

这显示了

idEmployee | name   |  idUser | 
    1      | John   |  5887Ab | 

我已经有了主管的id，现在如何将该Id与user-employee表中的名称进行匹配？

Answer 1

如果键不匹配，用户可以使用左连接

SELECT employees.*, e1.idUser as idSupervisor, e3.name as supervisor
FROM employees
left JOIN `user-employee` e1 ON  e1.idEmployee = employees.idEmployee 
left JOIN `employees-supervisor` e2 on on e2.idUser = e.idUser
left JOIN  employees e3 on e2.idEmployee = e3.idEmployee;

但是，如果表之间的键匹配，则应使用内连接

SELECT employees.*, e1.idUser as idSupervisor, e3.name as supervisor
FROM employees
INNER JOIN `user-employee` e1 ON  e1.idEmployee = employees.idEmployee 
INNER JOIN `employees-supervisor` e2 on on e2.idUser = e.idUser
INNER JOIN  employees e3 on e2.idEmployee = e3.idEmployee;