Posted by Peter Albertsson on 06/30/05 02:07

"Gnutt Halvordsson" <gnutt@shell.linux.se> wrote in message
news:Pine.LNX.4.63.0506281004410.11194@gnuttLap...
>
>
> On Mon, 27 Jun 2005, Peter Albertsson wrote:
>
>>
>> "Gnutt Halvordsson" <gnutt@shell.linux.se> wrote in message
>> news:Pine.LNX.4.63.0506272255520.8628@gnuttLap...
>>> I'm trying to devel a grouping system for certain individuals.
>>> The groupingsystem is meant to be built up like following
>>>
>>> Every group can have a finite number of child group, but only one
>>> parent.
>>> So there will be a tree-structure to it all.
>>> The grouping-system, I can work out. But the problem is, when viewing
>>> for
>>> example the root, all individuals should be shown.
>>>
>>> If I go down one branch, I should be able to see everyone, who is part
>>> of
>>> the group (which is the current branch) and all individuals placed on
>>> branches further out from the root than this branch.
>>>
>>> So I should always be able to see those In the group, and people above
>>> it.
>>>
>>> I have no idea on how to do this, so any help is appriciated.
>>>
>>> //Gnutt
>>
>> On this page you have 2 different solutions to your problem plus links to
>> a
>> 3rd solution:
>>
>> http://www.sqlteam.com/item.asp?ItemID=8866
>>
>> Adjency lists is the simplest way, but only allows you to see a fixed
>> number
>> of steps up or down the tree by using self-joins. Very ugly and not
>> dynamic.
>> Recursion is usually used to traverse the tree.
>>
>> Nested sets is a little more complicated but allows you to do more
>> advanced
>> stuff with the tree (including to see all childs at all levels for a
>> node),
>> but it unfortunately has some performance problems. If you don't expect
>> your
>> tree to be very large, I think nested sets is the best solution for your
>> problem.
>>
>> http://www.dbmsmag.com/9603d06.html
>>
>> http://www.dbmsmag.com/9604d06.html
>>
>> http://www.dbmsmag.com/9605d06.html
>>
>> http://www.dbmsmag.com/9606d06.html
>>
>> One can also use adjency lists with a "lineage" column, which I have
>> never
>> tried, but I don't THINK it will work very well for very deep trees.
>>
>> Nested sets and adjency lists can be combined to get around some of the
>> performance problems with nested sets..
>>
>> This is a huge topic, so google on 'sql hierarchies' to learn more about
>> it...
>>
>> Regards,
>>
>> Peter Albertsson
>>
>>
>>
>
> Thanks, this info gave me alot of ideas on how to solve other problems,
> but unfortunatly not this one =P
>
> These articles (as far as I can tell), lists all the parents to a specific
> node, what I want, is to see all the childrens to it.

You can do that with nested sets. Check out this power point file:

http://conf.phpquebec.org/slides/2005/Dealing_with_hierarchical.ppt

It includes table formats and some queries to do exactly what you want.

// Peter

[Back to original message]