mysql子查询存到另一张表,在MySQL中使用子查询到同一个表

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