Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Determine how many subsets in a nested set

Author  Topic  smpit Starting Member 1 Post Posted - 2007-05-09 : 14:25:27 Dear all, This is my first post on sqlteam forums. I hope i state my question clear enough. If not, please ask for any additional information. I am working with an hierarchic model of an organisation. It is quite a comprehensive model, containing about 50.000 organisational units. All units have one parent stored as ============================= |orgId | orgname | parentID| ============================= So far a schoool example of a hierarchic model. Now we are working with setting accessrights to certain organisations. An user can , for example, have access to a 2nd level organisation. Access rights are inherited from a parent. However it is possible that the access right changes to deny at for example a level 5 node (including children) this does in this case not apply to any other level 5 nodes. We've stored this in the database as. ============================= |userId| orgId| accessType| ============================= accessType can be grant or deny. Default behavior is deny all. At night we want to calculate the rights for all users (5000+) for each org unit to safe on the application server's performance. First thought was to loop through all org units for each user and write out a temporary table. This has however, a great impact on the performance. Now I was looking for an option to get only the orgId's who don't have an altered parent. in other words the parent of a subset I found a solution by using nested sets. 1 I have a list of all selected orgID's for a specific user 2 I select the node with the lowest left value this is is alwayts a subset parent. This goes in a temporary variable 2 I lookup the children of the former found node and remove these from my list including the subset parent. repeat steps above until there are no items left in the list I was however wondering if there is an easier, better performing option to handle this matter. Quite a long story, i hope all is clear. If not please let me know. And thanks in advance for the replies. Sincerly Hendrik van Agten