Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: