Should I Use a Self-Join

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...

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.

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

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 queries correctly. but i would advise to see if it is possible to rework your table, have a separate table linking people to each other 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