On fös, 2006-09-29 at 15:00 -0700, chester c young wrote:
> in a simple tree structured table
>
> table t(
> id primary key,
> pnt_id references t( id ),
> name
> );
>
> does anyone know an easy howbeit sneaky way of determining ancestory
> and decendency without recursive functions,
how about
CREATE TABLE ancestry ( ans_id int, desc_id int
)
for each record of t , for each ancestor of id, insert a record (ans_id,id) into anscestry
this can be maintained by application, or by triggers.
to get all ancestors of a particular id X:
SELECT name from t JOIN ancestry ON (id=ans_id) WHERE desc_id=X;
to get descendents:
SELECT name from t JOIN ancestry ON (id=desc_id) WHERE ans_id=X;
indexes on ancestry(ans_id) and ancestry(desc_id)
might be called for.
hope this helps
gnari