How to link two columns under new column in the same table

join;update

I want to link two columns under new column in the same table. For example: in my database there is an ID of the employee and there is another ID for his manager in the same table. Also, in the same table there is the name for each employee (including the managers). I want to link the employee ID with the ID of his manager. So, I can show the name of the manager under new column.

Thanks.

Best Answer

You can JOIN table Employee twice in this way:

CREATE TABLE Employee (ID int, Name varchar(50), ManagerID int);
GO
INSERT INTO Employee VALUES
(1, 'John', null),
(2, 'Peter', null),
(3, 'Joe', 1),
(4, 'Helen', 3);
GO
SELECT e1.ID,
       e1.Name,
       e1.ManagerID,
       e2.Name AS ManagerName
FROM   Employee e1
JOIN   Employee e2
ON     e2.ID = e1.ManagerID;
GO
ID | Name  | ManagerID | ManagerName
-: | :---- | --------: | :----------
 3 | Joe   |         1 | John       
 4 | Helen |         3 | Joe        

db<>fiddle here

Or use a LEFT JOIN if you would obtain all employees even if they haven't any manager.

SELECT      e1.ID,
            e1.Name,
            e1.ManagerID,
            e2.Name AS ManagerName
FROM        Employee e1
LEFT JOIN   Employee e2
ON          e2.ID = e1.ManagerID;
GO
ID | Name  | ManagerID | ManagerName
-: | :---- | --------: | :----------
 1 | John  |      null | null       
 2 | Peter |      null | null       
 3 | Joe   |         1 | John       
 4 | Helen |         3 | Joe        

db<>fiddle here