Hello chrisM@home,

There is one scenario in which the query is not returning me expected data when there is no any Parent exists in groupAssociation table like follows:

CREATE TABLE [dbo].[groupAssociations](

[Id] [int] IDENTITY(1,1) NOT NULL,

[GroupId] [int] NOT NULL,

[GroupName] [varchar](250) NULL,

[ParentGroupId] [bigint] NULL,

[ParentGroupName] [varchar](250) NULL

) ON [PRIMARY]

--Fixed sample data -> you missed "UNION ALL"

INSERT INTO [dbo].[groupAssociations]

([GroupId]

,[GroupName]

,[ParentGroupId]

,[ParentGroupName])

SELECT 3, 'B1', 2, 'B' UNION ALL

SELECT 4, 'B2', 2, 'B' UNION ALL

SELECT 2, 'B', 1, 'A'

-- here group A has no any entry in this table

in this case it should return A, B, B1, B2 when we give Group A to return all its associated groups including A;

;WITH rCTE AS (

SELECT Level = 1, Id, GroupId-- , GroupName, ParentGroupId, ParentGroupName

FROM dbo.groupAssociations

-- WHERE [GroupName] IN ('A') --('A','C','D')

where GroupId = 275

UNION ALL

SELECT Level = Level + 1, tr.Id, tr.GroupId-- , tr.GroupName, tr.ParentGroupId, tr.ParentGroupName

FROM dbo.groupAssociations tr

INNER JOIN rCTE lr ON lr.GroupId = tr.ParentGroupId

WHERE lr.Level < 8

)

SELECT GroupId-- , GroupName, ParentGroupId, ParentGroupName

FROM rCTE

GROUP BY GroupId-- , GroupName

ORDER BY GroupId

the above code return correct data with B i.e B, B1, B2

but when we pass group A, it does not return any group, the reason is because it don't have any row in groupAssociation table, but in my case we have it in groups table only. Group table is lookup table and its association table is groupAssociation table. what whould be the best solution in this case.