Re: Trees in SQL

Thank you all for your input. I will read and try over the week-end. I start with Kendall's proposition.

"Raymond W." <ray7866_at_yahoo.com> wrote in message news: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 Thu Jul 04 2002 - 04:19:39 CEST