CODEWITHSUNDEEP
sql-server-2008treecommon-table-expression
user1480192
user1480192

Reputation: 863

Multiple recursions in SQL tree

I am using SQL Server 2008. I have a table which details useraccess to groups with two columns - userid int and groupid int (let's call it tbl_access). Each user can have access to multiple groups at any level in the tree.

There is a second table of groups defining a tree structure with three columns - groupid int, groupfather int, groupname nvarchar(32) (top level groups have a value of -1 for groupfather) - let's call it tbl_groups.

I am trying to write a function where given a userid, the function will look up all the groups the user can access in tbl_access, and for each group it will then find all of child nodes, and return a combined distinct list of all the groups the user has access to).

I have tried using CTE but that only got me so far as getting all the child nodes of one group, not all the child nodes of all groups.

What i have for now is :

CREATEFUNCTION [DBO].[FUNC_ALL_CHILDGROUPS] (@GROUPID INT)
RETURNS TABLE
AS
RETURN (
    WITH ALLGROUPS (GROUPID, GROUPFATHER) AS 
    (
        SELECT GROUPID, GROUPFATHER 
        FROM 
            TBL_GROUPS 
        WHERE GROUPID = @GROUPID 

        UNION ALL 

        SELECT TBL_GROUPS.GROUPID, TBL_GROUPS.GROUPFATHER 
        FROM 
            TBL_GROUPS 
            INNER JOIN ALLGROUPS 
            ON TBL_GROUPS.GROUPFATHER = ALLGROUPS.GROUPID
    )
SELECT * FROM ALLGROUPS

Upvotes: 1

Views: 153

Answers (1)

Ivan Golović
Ivan Golović

Reputation: 8832

You can use a recursive CTE, for your function you can use the following query:

DECLARE @tbl_access TABLE (userid INT, groupid INT)
DECLARE @tbl_groups TABLE (groupid INT, groupfather INT)

INSERT  @tbl_access 
VALUES 
(1, 2),
(1, 3),
(2, 1),
(2, 4)

INSERT  @tbl_groups
VALUES  
(1, -1),
(2, 1),
(3, -1),
(4, 2),
(5, 4)

DECLARE @UserID INT = 1

;WITH cte AS (

    SELECT  a.*
    FROM    @tbl_groups a
    JOIN    @tbl_access c ON
            c.userid = @UserID
    AND     c.groupid = a.groupid

    UNION   ALL

    SELECT  b.*
    FROM    @tbl_groups b
    JOIN    cte d ON
            d.groupid = b.groupfather
)

SELECT  DISTINCT groupid
FROM    cte

Upvotes: 2

Related Questions