yes you are right Mr. 500

here is my sample code, it should return proper groups Ids, A is parent of all sub groups B and B is parent of B1 and B2, where B, B1 and B2 are in groupAssociation table but A is not in this table, but it exists in [Group] table coz it is not part of any child.

CREATE TABLE [dbo].[Group](

[Id] [int] NOT NULL,I

[GroupName] [varchar](250) NULL

) ON [PRIMARY]

INSERT INTO [dbo].[Group]

([Id], [GroupName])

select 1, 'A' union all

select 2, 'B' union all

select 3, 'B1' union all

select 4, 'B2' union all

select 5, 'B101' union all

select 6, 'C' union all

select 7, 'D' union all

select 8, 'X'

CREATE TABLE [dbo].[groupAssociations](

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

[GroupId] [int] NOT NULL,

[GroupName] [varchar](250) NULL,

[ParentGroupId] [int] 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' UNION ALL

--SELECT 1, 'A', null, null UNION ALL

SELECT 5, 'B101', 3, 'B1' -- UNION ALL

--SELECT 6, 'C', NULL, NULL UNION ALL

--SELECT 7, 'D', NULL, NULL

select * from [group]

select * from [groupAssociations]

;WITH rCTE AS (

SELECT Level = 1, ga.Id, isnull(GroupId, g.Id) as groupId-- , GroupName, ParentGroupId, ParentGroupName

FROM dbo.[group] g left join GroupAssociations ga on g.Id = ga.Id

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

where g.Id IN (1)

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

Please help me where i am doing mistake. I tried to get all groups and joined query with [Group] table but it messed up. I need to check with all possible scenarios. I was doing mistake when making my first sample code earlier.

. Please help...

Shamshad Ali