Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Question Unanswered: List All Employee under my control

    Hi,

    I have a table employee with two fields (empId, and Supervisor)

    what i want is to list all employees under a given empId

    example

    Table: Employee
    EmpId, Supervisor
    1, Null
    2, 1
    3, 1
    4, 2
    5, 2
    6, 3
    7, 4
    8, 4
    9, 5
    10, 5
    11, 6
    12, 6
    13, 6

    request of the employees under control of 4?
    Result should be: 4, 7, 8

    request of the employees under control of 2?
    Result should be: 2, 4, 5, 7, 8, 9 , 10

    request of the employees under control of 3?
    Result should be: 3, 6, 11, 12 , 13

    thanks a lots

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    declare @SupervisorID int
    set @SupervisorID = 3

    declare @Subordinates table (EmpID int)

    insert into @Subordinates (EmpID)
    select EmpID
    from Employees
    where Supervisor = @SupervisorID

    while @@Rowcount > 0
    insert into @Subordinates (EmpID)
    select Employees.EmpID
    from Employees
    inner join @Subordinates Subordinates on Employees.Supervisor = Subordinates.EmpID
    where not exists(select * from @Subordinates CurrentEmpIDs where Employees.EmpID = CurrentEmpIDs.EmpID)

    select * from @Subordinates

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •