I have a situation where I have an object called OrgUnit which has a list property of other OrgUnits. In this particular situation there is a 3 level deep heirarchy structure that represents a divion > region > territory. There are two tables that this data is stored in, the org_unit and the org_unit_relationship tables.

What I cannot seem to determine is if I can execute a query such that I need the top-down results (always starting at division), but the sub items are limited by a parameter, which in my case is the role of the user. For example, if the user role 'GM' signs on, they see their Division, all if that divisions regions and all of those regions' territories. However, if a user role of 'AE' signs in, they see their division, their region and their one territory.

I'm sure I can figure out the query (even through SQL) that could find the lowest part of the tree, but I need to return the heirarchy down to that part. In other words, it wouldn't be too difficult to find the territory for the AE, but I what I really need is the division containing the region containing the territory that I am after. This info is displayed in a tree control so I cannot go backwards in the result, with the territory containing the region containing the division.

Any thoughts on if this is even possible?