0

In my database (SQL Server) I have a table for employee information. Each employee has a manager. There is a hierarchical structure of employees in the table. Below is few demo data from this table

EmpId | Name    | Managerid
----- | ------- | ---------
1     | John    | 0
2     | Thomas  | 1
3     | William | 1
4     | Richard | 3
5     | Mary    | 0 
6     | Anne    | 4
7     | Matthew | 5
8     | Samuel  | 7
9     | Sarah   | 8
10    | Walter  | 5
11    | Ellen   | 3
12    | Amy     | 0
13    | Rachel  | 12
14    | David   | 4
15    | Lucy    | 3

My requirement is to find out the top most manager of each employee. So the result should look like

EmpId | Name    | ManagerName
----- | ------- | ---------
1     | John    | -
2     | Thomas  | John
3     | William | John
4     | Richard | John
5     | Mary    | - 
6     | Anne    | John
7     | Matthew | Mary
8     | Samuel  | Mary
9     | Sarah   | Mary
10    | Walter  | Mary
11    | Ellen   | John
12    | Amy     | -
13    | Rachel  | Amy
14    | David   | John
15    | Lucy    | John

How can I get this result.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Angshujit
  • 1
  • 1
  • Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Nov 06 '16 at 09:44
  • Thank you marc_s for helping me to frame the question properly. I was trying to do it but failed. – Angshujit Nov 06 '16 at 09:54
  • @Angshujit, Managerid should be NULL rather than zero to represent employees with no manager. That will allow a foreign key constraint to ensure only valid employees can be specified as managers. – Dan Guzman Nov 06 '16 at 10:48
  • Yes Dan, in my original table it is NULL only. By mistake I put 0 here. Anyway I found the answer from the above mentioned link. It works for me. Thanks a lot. – Angshujit Nov 06 '16 at 10:53

0 Answers0