Self Join and Iterative SQL

Question

Sample Table

Emp Id Emp Name Manger Id
1001   arun     1004
1002   Bharath  1004
1003   Chitra   1004
1004   Devi     1005
1005   Eli      1006
1006   Fatima   1007
1007   Ganesh   1008

when i select manager id 1004 it should display three names arun,bharath,chitra and if i select 1005 it should display devi,arun,bharath,chitra

FYI - ManagerId is same as the EMPId.

How can I prepare the sql with out using functions?

Answer 1

If you are using SQL Server 2005 and later you can use a common-table expression. In this example, I'm assuming that ManagerId being null indicates the top-most manager.

With HumanResources As
    (
    Select EmpId, EmpName, ManagerId, EmpName As ManagerName
    From Employees
    Where ManagerId Is Null 
    Union All
    Select E.EmpId, E.EmpName, E.ManagerId, H.EmpName
    From Employees As E
        Join HumanResources As H
            On H.EmpId= E.ManagerId
    )
Select EmpId, EmpName, ManagerId, ManagerName
From HumanResources

See Recursive Queries for more.