locked
Recursive query RRS feed

  • Question

  • User954998566 posted

    Hi, 

    I have a table Employee (Firstname, LastName, EmployeeID, SupervisorID)

    And I would like to display each employee and his supervisor

    Here's a sample of data

    John              doe         1234             8878
    Albert            Isa         1235             7752
    Sara              kelly       4423             5893
    Andrea            ford        8878             4456
    Bob               Trum        7752             4489
    Amir              Opet        4456             4423
    
    
    In this example, I want to retrieve all supervisors of John
    in that case 
    return query should return
    
    John              doe         1234             8878
    Andrea            ford        8878             4456
    Amir              Opet        4456             4423
    Sara              kelly       4423             5893
    

    With cte(FirstName,LastName,EmployeeID, SupervisorID) as
     (
    Select FirstName, LastName, EmployeeID, SupervisorID from Employee 
    union all
    Select cte.Firstname, cte.LastName, cte.EmployeeID, e.SupervisorID From CTE, Employee e
    where cte.SupervisorID = e.EmployeeID
    )
    select cte.* from  cte where 
    firstname = 'John' option (maxrecursion 0)

    I couldn't get the result that I'm looking for. 

    Any help on the query plz ?

    Thanks

    Thursday, January 7, 2016 4:10 PM

All replies

  • User452040443 posted

    Hi,

    Try:

    with CTE_H as
    (
        Select FirstName, LastName, EmployeeID, SupervisorID 
        from Employee 
        where FirstName = 'John'
    
        union all
    
        Select e.Firstname, e.LastName, e.EmployeeID, e.SupervisorID 
        From CTE_H as c
        Inner Join Employee as e
            On e.EmployeeID = c.SupervisorID
    )
    
    select * from CTE_H
    

    Hope this help

    Thursday, January 7, 2016 4:24 PM