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

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

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>...
Show quoteHide quote

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

Paul wrote:

Show quoteHide quote

Naming Conventions: Prefixing Columns w/ Table Names
Group By
column info from temp table
unique records
Oracle MINUS equivalent in MSSQL2000 ?