Using subquery for the same table in MySQL

Question

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

M.Ali · Accepted Answer · 2016-05-07 17:40:11Z

1

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

edited May 7, 2016 at 17:40

answered May 7, 2016 at 17:29

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

codeinprogress Over a year ago

Thanks, the first solution worked, but what if I want to do another left join. So there's another field in Staff called RoleId which looks up to Role table. I want to get the RoleName from Role table where idRole = RoleId

Bernd Buffen · Accepted Answer · 2016-05-07 17:28:49Z

0

LEFT JOIN is your friend. Try a query like this. Look at the aliases s(taff) and m(ember)

SELECT m.idStaff
     , m.Name
     , m.Email
     , m.Phone
     , s.Name
  FROM Staff m
  LEFT JOIN Staff s ON s.idStaff = m.SupervisorId
  ORDER BY m.Name ASC;

answered May 7, 2016 at 17:28

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Collectives™ on Stack Overflow

Using subquery for the same table in MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related