Nope.

SQL is set oriented and does not handle tree manipulation very well. Joe Celko published a solution in SQL For Smarties called Nested Sets. It works very well.

If you think that the depth of the tree will be small (< 15) you might just try walking the tree with a stack. SQL Server's table variables can be used to implement the stack. On my current project we are using this solution but out tree never exceeds 4 levels.

-Mike

>I have these tables: USER, GROUP, MEMBER AND GRANTED_PRIV.
>
>A USER can be a MEMBER of a(many) GROUP(s).
>A GROUP can be a MEMBER of a(many) GROUP(s).
>USERs and GROUPs can have GRANTED_PRIV.
>
>IN A SELECT STATEMENT (no T-SQL), if I want to retrive a USER GRANTED_PRIV along with all the GRANTED_PRIV granted to a GROUP in which the USER is a MEMBER and... along with all the GRANTED_PRIV granted to all GROUP member of a GROUP granted to the USER and so on......
>
> I could (this is not a valid syntax, just to explain my point):
>
>
>SELECT GRANTED_PRIV.*
> FROM GRANTED_PRIV GP
> WHERE GP.USER = my_user
>
>UNION
>
>SELECT GRANTED_PRIV.*
> FROM GRANTED_PRIV GP,
> MEMBER M
> WHERE M.USER = my_user
> AND GP.USER = M.GROUP
>
>UNION
>
>SELECT GRANTED_PRIV.*
> FROM GRANTED_PRIV GP,
> MEMBER M1,
> MEMBER M2
> WHERE M2.USER = my_user
> AND M2.GROUP = m1.USER
> AND GP.USER = M1.GROUP
>
>UNION
>
>SELECT GRANTED_PRIV.*
> FROM GRANTED_PRIV GP,
> MEMBER M1,
> MEMBER M2,
> MEMBER M3
> WHERE M3.USER = my_user
> AND M3.GROUP = m2.USER
> AND M2.GROUP = m1.USER
> AND GP.USER = M1.GROUP
>
>
>UNION.... you get the picture... but this is limited (in this example) to 3 level of nesting...
>
>
>DO you have a 'magic' ;-) statement that whould'nt be limited to a fixed level of nesting and also be performant...
>
>Thanks, have a good one!