Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Post Unanswered: Listing tasks for a particular manager and all his employees

    Hi,

    In a 3-level structure company, there is the president, some managers and employees with a table showing the boss as:

    Table: staff

    id name boss
    1 president NULL
    2 manager1 1
    3 manager2 1
    4 employee1 2


    There is another table for tasks assigned to either managers or employees:

    Table: tasks

    id assigned_to notes
    1 2 blah
    2 4 foo


    My question is: given a user, how to list tasks assigned to him and all employees reporting to him. For example, if the user is a manager, how to list tasks assigned to this manager and all his employees.

    Thanks,

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In a 3-level structure company, ...
    Use self-(outer)-join twice to know the employees reporting to the person.
    Then, use three left joins to know tasks assigned to the person and the employees.

    If there were more levels, it may be better to use other techniques.
    For example, recursive query.
    But, I don't know wheather(and how) MySQL supports recursive query or not.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using the adjacency model
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2012
    Posts
    2

    Thanks

    Thanks all for the replies. I will try to come up with the SQL statement to achieve that. Thanks again.

Posting Permissions

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