sql server programming: If this is a hierarchy of unknwn depth, you will need a different
model. Google up Nested Sets for trees.

I have a view that contains a self join:

SELECT dbo.Clients1.ClientID, dbo.Clients1.AccountName,
dbo.Clients1.OwnedByClientID,
Clients1_1.AccountName AS OwnedByClientName
FROM dbo.Clients1 INNER JOIN
dbo.Clients1 AS Clients1_1 ON
dbo.Clients1.OwnedByClientID = TCSClients1_1.ClientID

How can I also include a column that would count the number of
"OwnedByClientID"s that are owned by a ClientID?

TIA,

Paul

Thanks.

Actually, at this point it is only 2 or three deep, but you are correct, I
am interested in an arbitrarily deep nesting.
....<snip>...
[quoted text, click to view]

Paul,

In SQL Server 2005, finding this information is not
as hard as it used to be. Here's an example from
the AdventureWorks sample database that calculates
the total number of subordinate employees for each
manager. By default, recursive queries allow 100
levels of recursion before generating an error, though
this can be changed with the MAXRECURSION
option, if your hierarchy can be deeper.

with Ancestry(ManagerID,Subordinate) as (
select
ManagerID, EmployeeID
from AdventureWorks.HumanResources.Employee
union all
select
A.ManagerID,
E.EmployeeID
from AdventureWorks.HumanResources.Employee as E
join Ancestry as A
on A.Subordinate = E.ManagerID
)
select ManagerID, count(*) as subs
from Ancestry
group by ManagerID

-- Steve Kass
-- Drew University

[quoted text, click to view]