Fetching Names From One Table With Multiple Ids In Join

Employee ID Name 1 John 2 Williams Appointment ID DoctorName Employee_id Team lead 1 willson 1 2 SQL SELECT E.*,A.* FROM Employee as E,Ap

Solution 1:

Try this one

SELECT E.name
FROM Employee E
JOIN Appointment A 
ON A.Employee_id = E.ID
WHERE [somecondition here]

Solution 2:

IF Team Lead NAME IN Appointment TABLE THEN USE below QUERY:

SELECT E.Name AS'Employee Name',A.DoctorName AS'Doctor', A.TeamLeadName AS'Team Lead'FROM Employee AS E,Appointment AS A WHERE A.Employee_id = E.ID;

IF Team lead NAME IN differnt TABLE LIKE TeamLead THEN USE below QUERY:

SELECT E.Name AS'Employee Name',A.DoctorName AS'Doctor', T.TeamLeadName AS'Team Lead'FROM Employee AS E,Appointment AS A, TeamLead AS T WHERE A.Employee_id = E.ID AND A.TeamLead=T.TeamLead_ID;

If Williams is teamlead (which is not mention in your question) then use below query:

SELECT E.Name AS'Employee Name',A.DoctorName AS'Doctor', T.Name AS'Team Lead'FROM Employee AS E,Appointment AS A, Employee AS T WHERE A.Employee_id = E.ID AND A.TeamLead=T.ID;

Solution 3:

try this:

SELECT A.ID,A.DoctorName,E.Name,E1.Name 
FROM Employee E
INNERJOIN Appointment A
ON A.Employee_id = A.ID
WHERE E1.ID=A.Teamlead

Solution 4:

You have not used join. You need to use join here. Your TeamLead must be the same as Employee id. Based on that, you have to select the Employee name, and alias it as TeamLeadName.

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

Solution 5:

Try this:

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

The result will be:

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

See result in SQL Fiddle.

EDIT:

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

Sample Result:

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