We have a standard table of Users containing many fields, but the important aspects are:
We then have a Positions table which contains the following:
The PositionTypeID defines our organization levels. The following are example values
1 - Line Staff
2 - Team Lead
3 - Sr Line Staff
4 - Line Manager
5 - Sr Line Manager
What I'm trying to do is write a query that gives me the list of all Sr Line Staff or lower with their corresponding Line Manager. This would seem pretty basic, but the problem is, I cannot just assume, for instance, that someone in the Line Staff position type will have a Team Lead or Sr Line Staff above them. In fact, there could be multiple of each type (i.e., John could be a Line Staff type who works for Amy as a Team Lead who work for Susie as a Sr Line staff who works for Kate as another Sr Line Staff who works for Kevin as the Line Manager.) This is due to differing departments wanting to utilize the structural levels for different positions, wanting to skip them altogether, or any other reason. The position table is what separates they differing departmental positions and aligns them with the organizational structure.
Due to this, I cannot just simply say, go 3 levels up to find the Manager of a Line Staff employee. I have to recurse up the tree to find the first person listed as a Line Manager. (There could be cases where there are multiple people in the Line Manager position, but for these purposes, I'm going to ignore those situations.)
Is there any reasonably simple and efficient way to write this? It would be used all over the place in our application, reports, third part systems, etc., therefore, it has to work relatively quickly. Recursion is the only way I can think of to do this, but even if we can write the query, how well would it perform being run hundreds of thousands of times per day?