Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    13

    Unanswered: Heirarchical query - finding children of parents

    Ok...hopefully I can explain this -

    We have 3 tables, lets call them boss, leader, emp
    each with 1 -> many relationships:

    boss -> leader -> emp

    So, a boss can have many leaders and a leader can have many employees.

    I also have logical groups of bosses, leaders and emps. These groupings can be a mixture of any of the three:

    ie
    group1 boss1
    group1 emp3
    group1 leader2
    group2 boss2
    group2 leader1...etc

    To show this relationship we have a grouping table, which would show above, like the following:
    groupID------bossID-----leaderID-----empID
    1------------1
    1------------------------------------3
    1-----------------------2
    2------------2
    2------------------------1

    Now....on this table, there is a view which will determine which employees (lowest level) belong to which group, it does this by finding all of a bosses employees, and leader employees as well as groups employees - resulting a single mapping table:

    groupID- employeeID
    1------- 1 (if emp id belonged to boss1 for example)
    1------- 2
    1------- 3
    2------- 4
    2------- 5
    ....

    NOW....here's the problem, I want to add a "Child group" to the heirarchy - ie
    groupID------bossID-----leaderID-----empID---childgroupID
    1------------1
    1------------------------------------3
    1-----------------------2
    1---------------------------------------------2
    2------------2
    2-----------------------1

    Which means I need to join the table to itself and find all of it's members resulting in a new view:

    groupID employeeID
    1------- 1
    1------- 2
    1------- 3
    1------- 4 (inherited from group 2)
    1------- 5 (inherited from group 2)
    2------- 4
    2------- 5
    ....

    Note that this is a live system - so I can't redesign the layout of the tables. Hence why I'm creating a view.

    Any ideas???? - it's doing my head in!

    (sorry for all of the dashes - the site took away the extra spaces)

    Thanks
    Mark.
    Last edited by Tonka52; 10-19-05 at 09:14. Reason: Title change

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down


    There is no table which shows the leader->emp relationship.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Nov 2003
    Posts
    13
    I have not show them in my examples.

    The foreign keys are in each table respectively - ie

    EMPLOYEE
    id
    name
    ...
    leader_id


    LEADER
    id
    name
    ...
    boss_id

Posting Permissions

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