Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Get Parent And Subordinate Groups

DZone's Guide to

SQL Get Parent And Subordinate Groups

·
Free Resource

CREATE  FUNCTION dbo.fn_Get_All_Subordinate_And_Parent_Groups
(
@intGroupId int
)  
RETURNS @tblGroups Table (intGroupId int)
AS 
BEGIN
INSERT INTO 	@tblGroups
values		(@intGroupId)
WHILE EXISTS	(	SELECT 		nUserGroup_ID
			FROM		tblUserGroups
			WHERE		intParentId In (SELECT intGroupId FROM @tblGroups)
			AND		nUserGroup_ID NOT IN (SELECT intGroupId FROM @tblGroups)
		)
BEGIN
INSERT INTO 	@tblGroups
SELECT 		nUserGroup_ID
FROM		tblUserGroups
WHERE		intParentId In (SELECT intGroupId FROM @tblGroups)
AND		nUserGroup_ID NOT IN (SELECT intGroupId FROM @tblGroups)
END
WHILE EXISTS	(	SELECT 		nUserGroup_ID
			FROM		tblUserGroups
			WHERE		nUserGroup_ID In 	(
									SELECT	intParentId 
									FROM 	tblUserGroups 
									WHERE	nUserGroup_ID in (SELECT * FROM @tblGroups)
								)
			AND		nUserGroup_ID NOT IN (SELECT * FROM @tblGroups)
		)
BEGIN
	INSERT INTO 	@tblGroups
	SELECT 		nUserGroup_ID
	FROM		tblUserGroups
	WHERE		nUserGroup_ID In 	(
							SELECT	intParentId 
							FROM 	tblUserGroups 
							WHERE	nUserGroup_ID in (SELECT * FROM @tblGroups)
						)
	AND		nUserGroup_ID NOT IN (SELECT * FROM @tblGroups)
END

--REMOVE GROUP ID 1 (TOP LEVEL GROUP)
DELETE FROM @tblGroups WHERE intGroupId = 1

RETURN
END
Topics:

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}