Need to get Manager of Manager of Employees

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need to get Manager of Manager of Employees

Author

Topic

srinuganaparthi
Starting Member

1 Post

Posted - 2011-08-04 : 01:45:33

hi i Need to get all Employyes working under Managers

i have table structure like this

EmpId EmpName Email ManagerId

1 James James@gmail.com Null

2 Kenny kenny@gmail.com 1

3 Anderson Andes@gmail.com 2

4 Jasmin Jasmin@gmail.com 3

Here if i give EmpId as 1 i need to get all employees who are working under Employee James

suppose Kenny is working under James and Anderson is working under Kenny

Means Anderson is indirectly working under James

this way i need to get all the records

if i give EmpId as 1 i need to Kenny,Anderson and Jasmin Records

if i give EmpId as 2 i need to Anderson and Jasmin Records

if i give EmpId as 3 i need to get only Jasmin Record

I have writtend query but its giving only immediate Manager .

here is the query what i have written

SELECT e.EmpId,e.EmpName AS 'Employee Name',
e.Email,
m.EmpName AS 'Manager Name',
m.EmpId
FROM Employee AS e JOIN Employee AS m
ON e.ManagerID =m.EmpId
and e.EmpId=1

Please help me out,its very urgent

Regards
Srinivas Ganaparthi.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-04 : 02:35:38

you will need to use Recursive CTE

KH
[spoiler]Time is always against us[/spoiler]

tkatende
Starting Member

8 Posts

Posted - 2011-08-04 : 16:53:47

try using a self join

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources