Trees in SQL
Date: 1 Jul 2002 15:57:06 -0700
Message-ID: <5bd0d55e.0207011457.652672fb_at_posting.google.com>
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 Tue Jul 02 2002 - 00:57:06 CEST