Sql Server2005 problem

Question

EXACT duplicate of Sql Server2005 query problem

i have a table which contains the following fields

•Supervisorid •Empid This is just like a referral program. A guy can refer 3 guys under him i.e, 3 is referring three guys namely 4 5 8 similarly 4 is referring 9 10 and 11 likewise 8 is referring 12, 13 it goes like this..

I want a query to get all EmpId under Supervisor 3

Answer 1

Check this. It uses recursive store procedure so referring level will be limited .

A non recursive store procedure call solution.

With SQL2005 you can use Recursive Queries Using Common Table Expressions:

 WITH Report(Supervisorid , Empid , Title, Level)
    AS
    (
    -- Anchor member definition
        SELECT e.Supervisorid , e.Empid , e.Title,   0 AS Level
        FROM dbo.MyEmployees AS e
        WHERE Supervisorid = 3
        UNION ALL
    -- Recursive member definition
        SELECT e.Supervisorid , e.Empid , e.Title,   Level + 1
        FROM dbo.MyEmployees AS e
        INNER JOIN Report AS d
            ON e.Supervisorid = d.Empid 
    )
    -- Statement that executes the CTE
SELECT Supervisorid , Empid , Title, Level
FROM Report;