T-SQL Recursion; Multiple Recursions?

Question

I'm trying to conceptualize a solution to a problem I have with recursion and I can't quite wrap my mind around it. I have three tables. We'll call them DocGroup, GroupGroup, and GroupUser. In the DocGroup table there is a hirerachial structure of one record, which specifies another record as it's parent and so on until a record is its own parent.

Doc Group
1   1
2   2
3   2
4   3
5   2
6   4

GroupGroup contains Group nesting:

Group    MemberGroup
4        2
4        1

GroupUser maps a group to a number of users:

Group    User   Key
1        1      ABC
1        3      BCD
1        4      CDE
2        1      DEF
2        2      EFG
2        3      FGH
3        3      GHI
4        2      HIJ
4        3      IJK
4        4      JKL

So I would like to specify a user and a document and get a list of all the keys for that combination. For example if I choose user 2 and document 2 I need to return "EFG" but if I choose document 6 and user 3 I need to return "IJK", "GHI", "FGH", and "BCD" because group 2 and 1 are in group 4 and group 4 = document 4, which has a group of 3. In all cases I only need to get the record for the specified user.

I would imagine that I need multiple recursions to get this done, but I can't seem to mentally map out how that might be done in SQL and I don't want to degrade to loops and cursors to do this.

Answer 1

OK, here is the answer:

DECLARE @Key varchar(max)

;WITH DocBase AS (
    SELECT Doc, Group
        FROM DocGroup
        WHERE Doc = @Doc
    UNION ALL
    SELECT DocGroup.Doc, DocGroup.Group
        FROM DocGroup INNER JOIN DocBase
            ON DocGroup.Doc = DocBase.Group
        WHERE DocBase.Doc <> DocBase.Group --to prevent infinite recursion
    ), GroupNesting AS (
    SELECT Doc
        FROM DocBase
    UNION ALL
    SELECT MemberGroup
        FROM GroupGroup.Group = GroupNesting.Doc
    ), GroupList AS (
    SELECT DISTINCT Doc
        FROM GroupNesting
    ), KeyList AS (
    SELECT Key
        FROM GroupList INNER JOIN GroupUser
            ON GroupList.Doc = GroupUser.Group
        WHERE User = @User
    )
SELECT @Key = COALESE(@Key, '') + Key
    FROM Key

SELECT @Key

I could have used any of the suggestions from http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ for doing the final concatenation, but I chose this method because it is the easiest and I'm putting this in a procedure anyway.

Answer 2

I have to post this because I'm a raging egomanic:

The Zen of Recursion

But seriously, that's how we did it back in the day. These days, you'd use a Common Table Expression.

HTH.