Thread: tree-structured query

in a simple tree structured table<br /><br />table t(<br />  id primary key,<br />  pnt_id references t( id ),<br /> 
name<br/>);<br /><br />does anyone know an easy howbeit sneaky way of determining ancestory and decendency without
recursivefunctions,<br /><br />select name from t where exists <ancestor or descendent><br /><br />thanks for
insight<br/><p><hr size="1" />Get your own <a href="
http://us.rd.yahoo.com/evt=43290/*http://smallbusiness.yahoo.com/domains">webaddress for just $1.99/1st yr</a>. We'll
help.<a href="http://us.rd.yahoo.com/evt=41244/*http://smallbusiness.yahoo.com/">Yahoo! Small Business</a>.  

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