How to display project, group members and their boss side by side in mySQL

Question

I want to show (team id, team name, intern id, intern name, reporter id, reporter name) for all my records. I don't know how to separate interns in one column and reporters in another for same team. I want to do this because I want each reporter to provide evaluation for each intern they are working with in that group.

Answer 1

You could use a join with sub query for Intern and Reporter

select a.teamID, a.temaName, 
  b.peopleID intren_id, b.firstname intern_name, b.lastname inter_lastname, 
  c.peopleId, c.firstname reporter_name, c.lastname reporter_lastname
from mytable a  
inner join  (
  select teamId, firstname, lastname
  from mytable 
  where pType = 'Intern'
) b on a.teamId  = b.teamId 
inner join  (
  select teamId, firstname, lastname
  from mytable 
  where pType = 'Reporter'
) c on a.teamId  = c.teamId