I have a table called Staff which has the following fields: idStaff, Name, Phone, Email, SupervisorId.
The SuervisorId is the idStaff of that staff member's supervisor.
I want to show the list of all staff members with their basic info (Name, Email etc) as well as the name of their supervisor.
So something like this:
select idStaff
, Name
, Email
, Phone
, (select Name from Staff where idStaff = SupervisorId) as SupervisorName
from Staff
order
by Name ASC
The query does not work. I tried joining the two tables but I am confused on how to get the Name from the subquery in the join.
select idStaff
, Name
, Phone
, Email
from Staff a
inner
join Staff b
on a.idStaff = b.SupervisorId
order
by Name ASC
Any help will be much appreciated.
Maybe something like this....
select s1.idStaff
, s1.Name
, s1.Email
, s1.Phone
, s2.Name as SupervisorName
from Staff s1
LEFT JOIN Staff s2 ON s1.SupervisorId = s2.idStaff
order
by s1.Name ASC
or you could have done something like....
select s.idStaff
, s.Name
, s.Email
, s.Phone
, (select top 1 m.Name from Staff m
where s.SupervisorId = m.idStaff) as SupervisorName
from Staff s
order by s.Name ASC
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments