[thelist] Hierarchical SQL query

[Chris at globet.com]

All

I am working with SQL Server 2000.

I have a table Users as follows:

--------------------------
| UserId (PK) | ParentID |
--------------------------

I have a table Transactions as follows:

--------------------------------------------------------
| TransactionID (PK) | UserID (FK) | TransactionAmount |
--------------------------------------------------------

There is a recursive relationship within the Users table, whereby some
users are parents to other users (ParentID = UserId of parent user)
within the hierarchy thus:

         User1
           |
     --------------
     |            |
   User2        User3
     |            |
  -------      -------
  |     |      |     |
User4 User5  User6 User7

ONLY the users at the bottom of the tree may have transactions in the
Transactions table.

I need to write a query that will give me the sum of all transactions
for all users at the bottom of the tree for each user NOT at the bottom
of the tree. So: in the diagram above the value returned for User1 would
be the sum of all transaction amounts in the Transactions table for
User4, User5, User6 and User7. The value returned for User2 would be the
sum of all transaction amounts in the Transactions table for User4 and
User5, and the value returned for User2 would be the sum of all
transaction amounts in the Transactions table for User6 and User7.

I also need to write a query that does the same, but ONLY for the users
at the top of the tree. Thus, the only record returned for the diagram
above would be for User1. I would imagine that once I have the first
query sorted out, the second will be fairly trivial to implement as a
modification of the first.

I am becoming increasingly frustrated, so any pointers as to technique
would be most appreciated.

Chris Marsh
Web Developer
http://www.globet.com/
Tel: +44 20 8246 4804 Ext 828
Fax: +44 20 8246 4808

Any opinions expressed in this email are those of the individual and not
necessarily the Company. This message is intended for the use of the
individual or entity to which it is addressed and may contain
information that is confidential and privileged and exempt from
disclosure under applicable law. If the reader of this message is not
the intended recipient, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly prohibited.
If you have received this communication in error, please contact the
sender immediately and delete it from your system.