Retrieve Hierarchial Data

[wildsharkuk]

Hi I have a Hierarchial table with user_id + user_parent_id and would like to know how to retrieve / show only the bottom most child eg

Dave
  |
  +--Steve
       |
       +--Mark
       |
       +--Paul
            |
            +--Carl

so would need to return Mark and Carl as these are the bottom most children.

any help would be greatly appreciated

thanks

wildsharkuk

 

[alvechurchdata]

Assuming that you have got all your people in a single table which is joined to itself on ParentID=PeopleID then a sub-query like this might work:

Select PersonID From People 
  Where PersonID Not In (Select ParentID From People)

This will pull out all the people who do not appear as parents.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[fneily]

Hierarchial table"? What Database are you using? Not Access. Access is a Relational DBMS.
Here's one way your table should look like:
User_ID User Parent_ID
Then you can do a self-join to get who is child of who, or parent of who, etc.
Maybe someone else will accept your table structure and supply another answer.

 

[r937]

fneily, that's exactly how his table actually looks, it's called a hierarchical table because it stores hierarchical data (in what's called the adjacency model -- "parent_id" is the way this model works)

geoff, your query is close but will work correctly if you add a WHERE clause to your subquery --

select name 
  from HierarchicalTable
 where user_id not in
   ( select user_parent_id 
       from HierarchicalTable 
      where user_parent_id is not null )

here's another way --

select name 
  from HierarchicalTable as T
 where not exists
   ( select 1
       from HierarchicalTable 
      where user_parent_id = T.user_id )

r937.com | rudy.ca

 

[wildsharkuk]

Apologies for delay, been a meeting all morning.

to all thanks for your rapid response, the code works a treat thankyou very much.

regards

wildsharkuk