从一个具有多个ID的表中提取名称 [英] fetching names from one table with multiple ids in join

问题描述

员工

ID    Name   
1     John
2     Williams

预约

ID  DoctorName   Employee_id  Team lead
1    willson          1            2

SQL

SELECT E.*,A.* FROM Employee as E,Appointment as A WHERE A.Employee_id = E.ID

如果我想获取团队负责人姓名，我们将如何做到这一点?

This is my join if i want to fetch Team lead name how we can do that?

推荐答案

尝试一下:

SELECT A.ID,A.DoctorName,E.Name as Employee,E1.Name as TeamLead
FROM Appointment A INNER JOIN
Employee E ON E.ID=A.Employee_id INNER JOIN
Employee E1 ON E1.ID=A.TeamLead

结果将是:

ID  DoctorName  Employee_id   TeamLead
---------------------------------------
1   willson     John          Williams

在 SQL Fiddle 中查看结果.

See result in SQL Fiddle.

要包含更多表，您需要使用不同的别名连接Employee表:

For including more tables, you need to join Employee table with different alias name:

SELECT A.ID,A.DoctorName,E.Name as Employee,E1.Name as TeamLead,E2.Name as AttendedBy, Att.Attended
FROM Appointment A INNER JOIN
tblAttended Att ON Att.Id=A.Id INNER JOIN
Employee E ON E.ID=A.Employee_id INNER JOIN
Employee E1 ON E1.ID=A.TeamLead INNER JOIN
Employee E2 ON E2.ID=Att.employee_id

采样结果:

ID  DoctorName  Employee_id   TeamLead    AttendedBy     Attended
-----------------------------------------------------------------
1   willson     John          Williams    John           Y

这篇关于从一个具有多个ID的表中提取名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！