SQLTeam.com | Weblogs | Forums

Recursive SQL


#1

Hi,

I try to use an recursive SQL but I struggle and hope you can help me. I have the following Table:
Parent Name Type
CS App Global Key Users CS test1 1
CS test1 CS test 1
CS test rhorn 0

The table contains groups and users. Groups are from type 1 and groups from type 0. In the end I want a result with that shows the groups a user is member. In my example the expected result should be:

User group
rhorn CS test
rhorn CS test1
rhorn CS App Global Key Users

I tried the following

`WITH ALL1 AS (

Select ParentGroup, Name, Type From Groups Where Name='rhorn'

UNION ALL

Select G1.ParentGroup, G.Name, G.Type From Groups G

INNER JOIN ALL1 G1 ON G.ParentGroup = G1.Name

)

Select * from ALL1

I tried many things, but somehow I struggle and hope you can give me a hint?

Best regards,

Lara


#2

Hi,

I can filter now, but my recursive sql never stops:

ALL1 (a,d) AS (

Select DISTINCT ParentGroup AS a, Name As d From Groups

UNION ALL

Select ALL1.a, Groups.Name From ALL1, Groups Where ALL1.d=Groups.ParentGroup

)

Select a,d from ALL1 Where ALL1.d='rhorn'

Do you see my error or can give me a hint how I can set a stop condition?

Best regards,

Lara