Re: Tree structure in Relational DB design

Read Graeme Birchall's Db2 SQL Cookbook. He has a full-length chapter on Recursion which has several examples including the one you are looking for.

"Sean Donohue" <sgdonohue_at_hotmail.com> wrote in message news:adpubd$enp$1_at_hamble.qinetiq.com...
> I need to model a hierarchical, treelike relationship in Access. A simple
> example would be genealogy...lets say we have the following tables:-
>
> tblPeople
> ID Name other data
> 1 Paul ...
> 2 Peter ...
> 3 Steve ...
> 4 Mary ...
> 5 Emily ...
> 6 Sarah ...
>
> tblRelationships
> ParentID ChildID
> 3 1
> 3 5
> 1 4
> 6 1
> 5 2
>
> This seems to me to be a logical, normalised way of structuring the data.
> My question, however, is how do I write a query that will list the people
by
> generation? So the output I'd like would be
> Steve or Sarah
> Sarah or Steve ... the grandparents (order within the generation doesn't
> matter)
> Paul or Emily
> Emily or Paul ... parents
> Peter or Mary
> Mary or Peter ... children
>
> I guess I need to somehow calculate a generation value and then simply
order
> by that...it would be nice if I could get the database to calculate it
> instead of asking the user to input it. Or would I be better off with a
> different database structure? Any ideas please?
>
> Thanks,
> Sean
>
>
>
Received on Sat Jun 08 2002 - 04:19:40 CEST