--- In firebird-support@yahoogroups.com, "red_october_canada"
<red_october_canada@...> wrote:

>
> I have a Firebird 1.5.3 database with a table in it with the structure:
>
> PAR_RID NUMERIC(18,0),
> KEY_RID NUMERIC(18,0),
> LEVEL INTEGER DEFAULT 0,
> QTY INTEGER DEFAULT 0
>
>
> PAR_RID = Parent Record ID
> KEY_RID = Key Record ID
> LEVEL = Level in a tree
> QTY = Quantity
>
> The table is the basis for a "tree" structure.
>
> The LEVEL field is present just on a hunch that I might need it.
>
> My question is this... I have to SUM() all the QTY fields the root
> node, and all of it's children and all children of all their children,
> and their children, and their children, etc. etc. so that each 'node'
> can display only the sum of all it's own descendants and the only node
> that shows a total of every other node including itself, is the root

node.

>
> Is there a 'standard'/'well known' way (SQL statment) for doing this?

Oracle has a SQL extension to do this, but Firebird does not (yet).

I don't think you need LEVEL, providing a PAR_RID of null represents
there is no parent. We have something similar in our application (a
dynamically created menu). We handle this on the client side (for
other reasons) but our tree only has 100 records or so. One thing you
may wish to consider is moving the PAR_RID to another table. The
problem with a self referencing foreign key is that it is a pain to
work with. You need to insert the records in explicit order, where if
there are two tables:

ATree
(
KEY_RID,
LEVEL
)

ATreeRel
(
KEY_RID,
PAR_RID
)

You can simply insert the records into the Tree before you define the
relationships.

Anyway, the approach is reasonably straight forward, and PSQL should
be able to handle it.

Start with a procedure that accepts a KEY_RID, and generates a list of
KEY_RID values that have the input KEY_RID as a PAR_RID, then make it
recursively call itself for each of those keys.

And make 100% sure you don't get a loop.

Adam