CTE Tracking

  • Hello

    I'm trying to find a solution to my company personal structure, it's constantly changing and i need to set a flexible structure. So i was thinking in building a table for the teams, with something like this, ID, Team Name, other data, FatherID.

    This table can accommodate every change that the structure suffers, just need to change the FatherID for the team.

    I was looking for recursivity to retrieve all users (when i refer users, just need to get them from the users table that are relate to the team by the team id) in a team, and found out the CTEs and i can do some things, but there are some answers that i can't get, at least with my knowledge.

    One of the question is, how to get all users of a team, if the team is in level 1 and it has 2 other levels bellow, i need to get all the ids related to the team, level 1 id, level 2 ids dependent of level 1, level 4 ids dependent of level 2 ids.

    For example in level 0 i have MANAGERS, then in level 1 related to managers i have the SUPERIOR MANAGER, level 2 related to superior manager i have the MANAGER 1 and MANAGER 2 and finally level 3 i have one TEAM for each manager.

    What i want/need to know it's the people that belong to the teams related to the Superior Manager team.

    How can i do this? Is this even possible?

    Thanks

  • Yes, it can be done.

    However, I'd store the hierarchy in one place if possible.

    A team could have parent/child teams, but unless an employee can be in more than one team, each employee should just have a team ID assigned.

    That way, you just query the hierarchy of teams, and then join that to the employees table using the team IDs.

    That should do what you need.

    By the way, look into the HierarchyID datatype, instead of the traditional ID and ParentID model (adjacency hierarchy is the name for that).

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks for the answer, i didn't know the name of this to search in google.

    I have the employees/users in another table, i didn't realize that i didn't explained that. 🙂

    I'm just testing this in Sql Server 2008 and i don't know that it have a datatype HierarchyID, looks very good... :w00t:

    But i'll try to use the classic approach (adjacency hierarchy), because i don't know if the production server will be 2008 or 2005.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply