Sune Rievers blog

Today I had to deal with a child-parent table with a list of user groups. These groups are nested, and persons in a subgroup should have access to all information that users of parent groups have.

I tried to do this by using a function or stored procedure, but none of these were optimal solutions. In the end, I stumbled across the WITH statement in T-SQL.

From this blog and this, I learned that it is actually pretty easy to extract the list of children, given a parent id (or vice versa).

USE tempdb;
GO

DECLARE @t TABLE (
Code INT NOT NULL UNIQUE CLUSTERED,
[Name] VARCHAR(25),
Parent INT NULL
);

INSERT INTO @t VALUES(1,      'Name1',   NULL);
INSERT INTO @t VALUES(2,      'Name2',   1);
INSERT INTO @t VALUES(3,      'Name3',   NULL);
INSERT INTO @t VALUES(4,      'Name4',   2);
INSERT INTO @t VALUES(5,      'Name5',   3);

WITH hierarchy
AS
(
SELECT Code, Parent, [Name], 0 AS lvl, CAST('\' + LTRIM(Code) + '\' AS VARCHAR(MAX)) AS [path]
FROM @t
WHERE Parent IS NULL

UNION ALL

SELECT c.Code, c.Parent, c.[Name], p.lvl + 1 AS lvl, p.[path] + CAST(LTRIM(c.Code) + '\' AS VARCHAR(MAX)) AS [path]
FROM @t AS c INNER JOIN hierarchy AS p ON c.Parent = p.Code
)
SELECT (REPLICATE(SPACE(1), lvl * 4)) + [Name]
FROM hierarchy
ORDER BY [path];
GO

Above code is from http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/865bcee0-dbe0-4a4e-98ab-82ae6c802df9

This is actually pretty sweet! With proper indexes and database structure, this runs almost as fast as when joining on the top level (root parent id)!

The code for the view ended up like this:

CREATE view [dbo].[GroupLevels]

as

WITH hierarchy
AS
(
SELECT departmentId, parentDepId, departmentDesc
FROM UserGroups
WHERE parentDepId IS NULL

UNION ALL

SELECT c.departmentId, c.parentDepId, c.departmentDesc
FROM UserGroups AS c INNER JOIN hierarchy AS p ON c.parentDepId = p.departmentId
)
SELECT departmentId, parentDepId
FROM hierarchy

Now I can use this simple join statement in order to join on sub groups as well (GroupLevels is my view):

SELECT * FROM
ACCESS A,
USERS U
INNER JOIN Membership M ON M.Group IN
   (SELECT departmentId FROM GroupLevels g
        WHERE g.parentDepId = A.intGroupID)
AND M.account = U.account
AND A.level = 1