Q

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.


This was first published in April 2004
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close