Suppose you have children, parents, grandparents and great grandparents.
If each parent have 10 children, then you either have to examine
10+100+1000 (going from great grandparents downwards) or 2+4+8 (going
from child upwards - assuming all children have two parents) to find if
a person is an ancestor of the other.

I don't think the overhead of a recursive stored procedure is big, you
just have to ascertain that there is no circular relationship (infinite
loops should be avoided). Even though I've no experience myself, it is
the standard way of solving such problems as yours on this list.

HTH,
Set

chrisacron wrote:

> Thanx for the response Milan.
>
> I had thought of that but wouldn't a recursive stored procedure incur
> an even bigger overhead? As each level fires a select statement that
> has to run for each row returned?
>
> Cheers
>
> Chris
>
>
> --- In firebird-support@yahoogroups.com, Milan Babuskov <milanb@...>
> wrote:
>> chrisacron wrote:
>>> In my application I use a self-related table to define complex
>>> classifications. The number of levels are theoretically unlimited but
>>> in reality will probably be 10-15 maximum.
>>> Is there another way of doing this?
>> Write a recursive stored procedure?
>>
>> With recursive SP you are not limited and go to any depth (or whatever
>> is the limitation of stack space).
>>
>> --
>> Milan Babuskov
>> http://swoes.blogspot.com/
>> http://www.flamerobin.org