Should I Use a Self-Join

Question

If I have a table...

  ID     Name   Manager 
  0      Joe    Sue  
  1      Jake   Tom
  0      Joe    Tom
  2      Larry  Red
  2      Larry  Paul
  1      Jake   Paul

I want the output to be....

 ID   Name   Manager1    Manager2
 0    Joe    Sue         Tom
 1    Jake   Tom         Paul
 2    Larry  Red         Paul

Thanks...

Answer 1

If I have understood your request properly, yes, something like would produce the results you are looking for.

SELECT
t1.Name Name,
t1.Manager Manager1,
t2.Manager Manager2
FROM
Table t1
inner join Table t2 on t1.Manager = t2.Name

Of course a foreign key back to the index column would be preferential to strong comparisons for performance.

Answer 2

Yeah, if your table was called 'Managers':

SELECT Mgr1.ID,Mgr1.Name,Mgr1.Manager,Mgr2.Manager
FROM Managers AS Mgr1
LEFT JOIN Managers AS Mgr2
ON Mgr1.ID=Mgr2.ID

Answer 3

If your keeping the tables a join would be best.

If you hate joins, you could combine the max and min managers, and even then it would work if there is always 2 managers and they can't have the same name.

the below should work if i remember how to join up 2 querrys correctly. but i would advise to see if it is possible to rework your table, have a seperate table linking people to eachother in a manager employee relation.

SELECT DISTINCT F.ID, F.Name, S.Manager, F.Manager
FROM (select ID, Name, MIN(manager) manager FROM Managers Group by ID, Name) F,
(select ID, Name, MAX(manager) manager FROM Managers Group by ID, Name) S
WHERE F.ID = S.ID
and S.Manager <> F.Manager
and F.ID < S.ID