mysql - 实现3表连接

Question

我有3张 table

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

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

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

我想要下一张 table

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 与用户-员工表中的姓名相匹配？

Answer 1

如果key与我们不匹配可以使用左连接

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;