SQL Stored Procedure - building an heirarchy

[Coxy101]

Hi,

I need help,

I currently have a table in SQL that consists of an id and parentID to build a heirarchy, If a user has access to one of the items in the table then i want them to also gain access to its children (could be many levels of children).

Therefore i am supplying the user id and matching them to all items they have access to, however, the problem arises when attempting to identify its children.

I simply want to return all items that the user has direct access to and also the ones it inherits because it is a child.

For example,

Item 1 -- user has access to this item
->Item 1a
->Item 1b
->Item 1b(i)

I want the SP to return all four items even though they only have access to 'Item 1'.

Hope someone can help,

Cheers,

Kenny.

 

[LogicMagic]

The best way I found to do hierarchies is not to do it by autonumber id but inbed level info into the ID.

So your example would have ids that looked like

1
1_a
1_b
1_b_i

and then you can do
select *
from table
where ID = '1' or ID like '1_%'

I hope you can see how this would allow you to find all the children of any record easily.

Vlad

http://www.logicmagic.com

 

[nigelrivett]

Have a look at

http://www.nigelrivett.net/RetrieveTreeHierarchy.html

The temp table #tree has what you want I think.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.