Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    137

    Unanswered: How to SELECT direct and indirect staff

    Hi,

    I have a Marketing & Sales Director, the Marketing & Sales Director has one Marketing Manager and Sales Manager reporting to him. The Sales Manager has two Sales Executives and the Marketing Manager has two Marketing coordinators reporting to them.

    I can make a SELECT to list staff reporting to the Sales Manager and staff reporting to the Marketing Manager.

    I can also make a SELECT to list staff reporting to the Marketing & Sales Director (which are Marketing Manager and Sales Manager).

    Now I want to make a SELECT to list all staff reporting directly on indirectly to the Marketing & Sales Director including staff reporting to managers under the Marketing & Sales Director so the query should list the below when I query for staff reporting to the Marketing & Sales Director:

    1. Marketing Manager
    2. Sales Manager
    3. Sales Executive 1
    4. Sales Executive 2
    5. Marketing Coordinator 1
    6. Marketing Coordinator 2

    How can I do this please?


    Thanks,
    Jassim

  2. #2
    Join Date
    Oct 2007
    Posts
    52
    Provided Answers: 3
    can you give us your table layout? Just from my point of view, I would think you query the table getting the employees of your Marketing & Sales Director, then left outer join to the table getting the staff of those people if they exist. This approach, however, wouldn't catch staff of the next level of subordinates. Also, you'd have to play with it as using just a generic outer join, the second level of employees would just be another column and the immediate staff would be listed multiple times. Similar to:

    Sales Manager Sales Executive 1
    Sales Manager Sales Executive 2
    Marketing Manager Marketing Coordinator 1
    Marketing Manager Marketing Coordinator 1

  3. #3
    Join Date
    Apr 2003
    Posts
    137
    In employees table:

    1. employee_number
    2. first_name
    3. last_name

    In employee_job

    1. employee_number
    2. reporting_to (refers to employees->employee_number)

  4. #4
    Join Date
    Oct 2007
    Posts
    52
    Provided Answers: 3
    then like I said the easiest way is to left outer join to the table to get first level of subordinates. This will give you a start and you can take a look at recursion to get further down levels of staff. To start you off try something along these lines:

    Code:
    select a.last_name, c.last_name
       from employees a
    inner join employee_job b
      on b.employee_number = a.employee_number
     and b.reporting_to = ???
    left outer join employee_job d
        on a.employee_number = d.reporting_to 
    inner join employees c
       on d.employee_number = c.employee_number
    Dave Nance

Posting Permissions

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