-1

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.

  • Please tag your DBMS. – McNets Jul 22 '18 at 16:52
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        

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

  • thank you very much. But how can I put the second option inside another (select ) code. I really need help on that. – Eman Jul 26 '18 at 7:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.