Problem solve Get help with specific problems with your technologies, process and projects.

Counting rows in a subtree

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.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.