In SQL, how do I recursively select all the groups under a parent group?

Question

Originally posted by Unknown

I want to select all the groups under this group.

Answer 1

Originally posted by Unknown

Replace 155256 with the parent group id:

WITH CTE AS
(
    SELECT Id, ParentGroupId
    FROM [Group]
    WHERE ParentGroupId = 155256
    UNION ALL
    SELECT G.Id, G.ParentGroupId
    FROM [Group] G
    INNER JOIN CTE ON G.ParentGroupId = CTE.Id
)
 
SELECT G.*
FROM CTE C
INNER JOIN [Group] G ON G.Id = C.Id

Note: This can also be adapted for Locations or other tables that use a parent id.

Answer 2

Originally posted by Unknown

Adding to this, here are other examples for different tables (for ease of copying and pasting)

Location

WITH CTE AS
(
    SELECT Id, LocationGroupId 
    FROM [Location]
    WHERE LocationGroupId = 155256
    UNION ALL
    SELECT L.Id, L.LocationGroupId 
    FROM [Location] L
    INNER JOIN CTE ON L.LocationGroupId = CTE.Id
)
 
SELECT G.*
FROM CTE C
INNER JOIN [Location] L ON L.Id = C.Id

Page

WITH CTE AS
(
    SELECT Id, ParentPageId
    FROM [Page]
    WHERE ParentPageId = 731
    UNION ALL
    SELECT P.Id, P.ParentPageId
    FROM [Page] P
    INNER JOIN CTE ON P.ParentPageId = CTE.Id
)
 
SELECT P.*
FROM CTE C
INNER JOIN [Page] P ON P.Id = C.Id

Group Type

WITH CTE AS
(
    SELECT ChildGroupTypeId, GroupTypeId
    FROM [GroupTypeAssociation]
    WHERE GroupTypeId = 177
    UNION ALL
    SELECT GTA.ChildGroupTypeId, GTA.GroupTypeId
    FROM [GroupTypeAssociation] GTA
    INNER JOIN CTE_GT ON GTA.GroupTypeId = CTE_GT.ChildGroupTypeId
)

SELECT GT.*
FROM CTE C
INNER JOIN [GroupType] GT ON GT.Id = C.ChildGroupTypeId