Trees in SQL

I have the following tables:

Table: Corp

Fields:     LEC#
            Name
            CashonHand

Sample data:

LEC#   Name     CashOnHand
1      Parent   100
2      Sub1     200
3      Sub2     250
4      Sub1a    300
5      Sub1b    450
6      Sub2a    400
7      Sub3     300

Table: Relationship
Fields:      LEC# 
             SubLec#
             Relation

Sample Data:

LEC# SubLec# Relation

1    2         80%
1    3         80%
2    4         100%
2    5         80%
3    6         80%
4    7         50%
6    7         50%

Table relationship indicates which entity owns which and by what percentage. For example, in the sample data above, LEC1 owns 80% of LEC2 and 80% of LEC3, LEC 2 owns 100% of LEC4 and 80% of LEC5,...and so on. Please note the split relationship in entity LEC7 (i.e., LEC 4 and LEC5 each own 50% in LEC7) which is causing some problems in the design of my databases/queries.

The scope of my project is to know at each entity level how much cash is on hand at each level taking into consdieration all the subs underneath. For example, Sub1b would just be its own cash of 450. However, Sub1 would be 1010 (80%x450+50%x300+100%x300+own cash of 200). I would need this report for each sub.

I have looked into "nested sets" that Celko proposes: http://www.intelligententerprise.com/001020/celko.shtml but I have problems figuring how to apply the ownership % and also how to deal with the split ownership (how does the "worm go around the box" in this case).

I am fairly new to SQL (using Acess 2000) and would appreciate any input. Received on Mon Jul 01 2002 - 17:57:06 CDT