仅使用主键和外键获取值

Question

我已经尝试过几次queries和joins，但我解决不了这个问题。

我有两张桌子

Employee
--------
ID(PK)                                                                                                                                     
Firstname                                                                                                                                                  
Lastname                                                                                                                                                  
ContactNumber                                                                                                                                                  
Position                                                                         
TeamleaderID(FK) //*which is only applicable if the position isn't a team leader*//

Team Leader
----------
ID(PK)                                                                         
employeeID(FK)  

Employee表由两个数据组成，第一个是。

ID(PK) - 1                                                                         
Firstname - Mikael                                                                                                                               
Lastname - Roque                                                                                                                     
ContactNumber - 0010101                                                                                                             
Position - TeamLeader                                                                                                                                                                                              
TeamleaderID(FK) - Null 

第二个是

ID(PK) - 2                                                                         
Firstname - Rinnie                                                                         
Lastname - Hoshino                                                                         
ContactNumber - 0010101                                                                         
Position - Engineer                                                                         
TeamleaderID(FK) - 1

第三张是teamleader表

ID(PK) - 1                                                                         
employeeID(FK) - 1   

我试过这个查询

SELECT employee.*                                                                         
FROM employee                                                                         
JOIN teamleader                                                                         
ON employee.teamleader_id=teamleader.teamleader_id                                                                         
Where employee.firstname='Rinnie';  

但结果是只显示了teamleader id。是否可以选择所有的employee数据，包括teamleader的名称

Answer 1

请按如下所示尝试查询。您发布的查询的问题是，您不是在选择组长的姓名，而是选择原来的员工。要获得团队负责人的名称，您必须加入到employee表(employee_1)的另一个实例，并链接到表teamleader中的employeeId字段。

SELECT e.*, m.FirstName as TeamLead
FROM employee e
join teamlead on e.TeamleaderId = teamlead.ID
join employee m on teamlead.employeeID = m.ID
WHERE e.FirstName = "Rinnie";

干杯,

Answer 2

您需要另一个连接才能获得结果：

SELECT employee.*,leader.*
FROM employee
LEFT JOIN teamleader ON employee.teamleader_id=teamleader.teamleader_id
LEFT JOIN employee as leader ON teamleader.employeeID = leader.ID
Where employee.firstname='Rinnie';

我为没有团队领导的员工添加了“左加入”。