DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone >

SQL Get Parent And Subordinate Groups

Snippets Manager user avatar by
Snippets Manager
·
Jan. 12, 07 · · Code Snippet
Like (0)
Save
Tweet
828 Views

Join the DZone community and get the full member experience.

Join For Free

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
sql

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • The 5 Healthcare AI Trends Technologists Need to Know
  • Growth in Java Development for Web and Mobile Apps
  • 6 Best Books to Learn Multithreading and Concurrency in Java
  • Maven Tutorial: Nice and Easy [Video]

Comments

Partner Resources

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo