I am currently in the process of developing a Delphii 5 application to work against a SQL Server 7.0 database. I have the following tables :
USERGROUPS (to detail the groups that system users are associated with - i.e. 'Department1', 'Department2', 'Slaves').
USERSUBGROUPS (such that one group can be contained within another, for instance 'Entire Company' will include the subgroups 'Department1', etc).
USERGROUPPEOPLE (to detail those individuals contained within each group).
Suppose I have an individual 'Slave1' who is contained within the 'Slaves' group (which in turn is a subgroup of 'Entire Company').
Using SQL syntax I am able to obtain a result set indicating that the given individual is a member of 'Slaves' and also of 'Entire Company' (through the fact that 'Slaves' is a subgroup of 'Entire Company'). This is all well and good (for this finite situation) but ideally I am looking for a limitless idea for instances when an individual is a member of a group 'Group1', which is a subgroup of 'Group2', which in turn is a subgroup of 'Group3', which in turn is a subgroup of 'Group4', etc. I would like to be able to obtain a result set indicating that (given 'Person1' is a member of 'Group1') the groups to which 'Person1' is part of are 'Group1', 'Group2', 'Group3'. 'Group4', etc. Is there any way of doing this recursively (such that I am not binding myself to a certain number of levels in the SQL syntax) or is that just not achievable.
Any pointers would be greatly appreciated.
Thanks in advance.
Steve
USERGROUPS (to detail the groups that system users are associated with - i.e. 'Department1', 'Department2', 'Slaves').
USERSUBGROUPS (such that one group can be contained within another, for instance 'Entire Company' will include the subgroups 'Department1', etc).
USERGROUPPEOPLE (to detail those individuals contained within each group).
Suppose I have an individual 'Slave1' who is contained within the 'Slaves' group (which in turn is a subgroup of 'Entire Company').
Using SQL syntax I am able to obtain a result set indicating that the given individual is a member of 'Slaves' and also of 'Entire Company' (through the fact that 'Slaves' is a subgroup of 'Entire Company'). This is all well and good (for this finite situation) but ideally I am looking for a limitless idea for instances when an individual is a member of a group 'Group1', which is a subgroup of 'Group2', which in turn is a subgroup of 'Group3', which in turn is a subgroup of 'Group4', etc. I would like to be able to obtain a result set indicating that (given 'Person1' is a member of 'Group1') the groups to which 'Person1' is part of are 'Group1', 'Group2', 'Group3'. 'Group4', etc. Is there any way of doing this recursively (such that I am not binding myself to a certain number of levels in the SQL syntax) or is that just not achievable.
Any pointers would be greatly appreciated.
Thanks in advance.
Steve