TSQL to recurse based on a condition

Question

I have a query to get list of members in a group as below

;WITH CTE (GroupName, GroupMember, isMemberGroup)
     AS (SELECT ag.name,
                agm.Member,
                agm.MemberIsGroup
         FROM   tb1 ag
                LEFT JOIN tb2 agm
                  ON ag.ID = agm.ID
         WHERE  ag.name = 'somegroupame')
SELECT *
FROM   CTE

Group Name   Group Member  IsMemberGroup
Admin        John          0
Admin        Sam           0
Admin        GDBA          1
xyz          Dan           0 
xyz          GXy           1

I want to write a query to get members of the sub group as well if IsMemberGroup is 1. Please guide me how to achieve that.

The expected result is to get a list of members including members of sub groups for a give group. The recursion should happen for all the sub groups' groups as well if any:

Resultant: Admin

     Group              Group Member
     Admin              John
     Admin              Sam
     Admin(GDBA)        Mike
     Admin(GDBA)        June
     Admin(GDBA/Bcksdmin)Mark

Is it maximum one level of recursion? Also please give example data for `tb1` and `tb2` along with the desired results for that example data. — Martin Smith, Dec 29 '15 at 21:20
@MartinSmith Yes. When the IsMember group column is one, the same query needs to be executed for that sub group and return members of the sub-group. tb1 contains group names and tb2 contains members of the group. — RMu, Dec 29 '15 at 21:22
@MartinSmith If I give a group name the result should contain all group members(Including members of sub group) — RMu, Dec 29 '15 at 21:24
Add the info to your question in tabular form so there is no ambiguity. — Martin Smith, Dec 29 '15 at 21:24
It's unclear from your post what the actual underlying table structure is. — Tom H, Dec 29 '15 at 21:40
try this http://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join-in-sql-server — techspider, Dec 29 '15 at 21:49
@TomH I'm joining two tables based on group id ,one table have all the group names and the other have all group members. — RMu, Dec 30 '15 at 14:27
Rather than explain it, the structure would be much clearer to everyone I believe if you just included a couple lines of the DDL code to create the tables. That also makes it easier for people who want to help to be able to simply copy and paste your code and try to work out a solution. — Tom H, Dec 30 '15 at 15:34

score 1 · Accepted Answer · answered Dec 30 '15 at 15:37

You could solve this problem using a recursive CTE to generate your group member hierarchy.

This example using the following data:

/* Let's create some sample data to experiment with.
 */
DECLARE @Sample TABLE
    (
        GroupName            VARCHAR(50),
        GroupMember            VARCHAR(50),
        IsMemberGroup        BIT
    )
;

INSERT INTO @Sample
    (
        GroupName,
        GroupMember,
        IsMemberGroup
    )
VALUES
    ('Admin', 'John', 0),
    ('Admin', 'Sam', 0),
    ('Admin', 'GDBA', 1),
    ('GDBA', 'Mike', 0),
    ('GDBA', 'June', 0),
    ('GDBA', 'Bcksdmin', 1),
    ('Bcksdmin', 'Mark', 0)
;

The query uses the CTE to calculate the group hierarchy. This is then joined back to the original data, like so:

/* Using a recursive CTE we can build the group name
 * hierarchy.
 */
WITH [Group] AS
    (
            /* Anchor query returns all top level teams, ie
             * those that do not appear in the group member
             * field.
             */
            SELECT
                s1.GroupName,
                CAST(s1.GroupName AS VARCHAR(MAX))        AS Breadcrumb
            FROM
                @Sample AS s1
                    LEFT OUTER JOIN @Sample AS s2    ON s2.GroupMember = s1.GroupName
            WHERE
                s2.GroupName IS NULL
            GROUP BY
                s1.GroupName

        UNION ALL

            /* Using recursion, find all children.
             * (Sub groups are identified by the IsMemberGroup flag).
             */
            SELECT
                s.GroupMember                            AS GroupName,
                g.Breadcrumb + '/' + s.GroupMember
            FROM
                [Group] AS g
                    INNER JOIN @Sample AS s        ON s.GroupName = g.GroupName
            WHERE
                s.IsMemberGroup = 1                
    )
SELECT
    g.Breadcrumb,
    s.GroupMember        
FROM
    @Sample AS s
        INNER JOIN [Group] AS g        ON g.GroupName  = s.GroupName
WHERE
    s.IsMemberGroup = 0
;

Although this solution works I would suggest you revisit the table design, if possible. The column GroupMember is pulling double duty by storing both teams and team members. Splitting this into Group (with parent lookup column) and GroupMember tables would simplify the task. Each new table would only need to describe one real world object, making it clearer where new columns should be created.

Thank you! This solution worked...And yes I will look into the table design. — RMu, Dec 30 '15 at 18:31

TSQL to recurse based on a condition

1 Answers1