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

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.

1 Answer 1

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

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