Connect By in SQL Server 2000

Hi

I realise that this is the Oracle group, but I am in search of a decent PL/SQL to T-SQL translator...

Here goes...

I am trying to run a query in an SQL Server 2000 database.

Simply, with regards to the application, I need to see how many children there are under the parent.

Apparently, in Oracle you might use something called Connect By:

E.g.

select dataid, name
from dtree
start with dataid = 12104040
connect by prior dataid = parentid

So where the parent had an identification number, so does the child. I need to see how many children, in total, are under the parent. I guess one might use hierarchical queries but I wouldn't really know where to start without having to type pages and pages of T-SQL. Is there an easier solution?

As ever, a pint of Champagne to the winner and a Magnum of Bud to all others for assistance... ;-)

Thanks in advance.

Simon Received on Mon Nov 04 2002 - 08:23:18 CST