There is a table called User (userID PK, userName, supUserID FK). SupUserID refers to userID and contains the userID value of the user's superior. The highest user has no user superior to itself (NULL instead of a value) and can have under itself one or more users or no users at all. Number of levels in such a tree is not defined.
Is there a way to count all users under the highest user by using SQL only (not Transact-SQL) or something else? Hope it's a solvable question in general.
In general, this requires recursion. Yours is the classic hierarchical structure, implemented using the adjacency model. As I mentioned in a previous answer, Recursion in SQL, only Oracle, to my knowledge, provides a way to do recursion.
Of course, if you can store your data using the nested set model, then the solution is straight-forward, I'm told (I don't actually use the nested set model myself). See Converting an adjacency list model to a nested set model.
If you must stick with the adjacency model, you can "work around" the need for recursion, provided you are able to set a realistic limit to the depth of the subtree. Just use an appropriate number of self-joins. If the path from the CEO to the lowliest cube jockey is eight layers of management, then have nine self-joins. Most databases are pretty good at joining tables, handling even a dozen or two with ease.
This was first published in April 2004