SQLTeam.com | Weblogs | Forums

SQL Help

Hello,

I need help creating a query that creates a security profile to assign to a person. Here is the scenario:

Manager security profiles should be configured based on the org structure. Example:
Manager A ID 6709 reports to Manager B ID 8790. Manager B reports to Manager C 6765. Manager C 8787 reports to Manager D. Manager D reports to Manager E 9089. Manager E reports to Manager F 0907

Manager A security profile = 6709
Manager B security profile = 6709, 8790
Manager C security profile = 6709, 6765, 8790
Manager D security profile = 6709, 6765, 8787, 8790
Manager E security profile = 6709, 6765, 8787, 8790, 9089
Manager F security profile = 6709, 6765, 8787, 8790, 9089, 0907

How would I accomplish building the profiles using SQL if I have a table that contains the following fields:
Employee ID, Name, Manager ID

So, for the manager A security profile, he/she would receive a list of employees with 6709 as the manager id in this file.
Manager B would receive a list of employees with 8790 but also the employees who have a value of 6709 for manager I since manager A reports to Manager B.

Etc.

Thanks in advance!