Recusion on Group and their subGroups- how can I solve this problem?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » T-SQL (SS2K8) » Recusion on Group and their subGroups- how...

Add to briefcase

17 posts, Page 2 of 2

««1 2

Recusion on Group and their subGroups- how can I solve this problem?

Expand / Collapse

Author

Message

Posted Friday, January 06, 2012 4:32 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 12, 2012 10:21 PM
Points: 526, Visits: 588

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.

Post #1231376

Posted Friday, January 06, 2012 4:41 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 921, Visits: 3,815

Shamshad Ali (1/6/2012)

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 ....

Since you already have entities in the groupAssociation table which have no parent, shouldn't these be in the groupAssociation table also?

^{Low-hanging fruit picker and defender of the moggies}

For better assistance in answering your questions, please read this.

Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Post #1231380

Posted Friday, January 06, 2012 7:48 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 12, 2012 10:21 PM
Points: 526, Visits: 588

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.

If a group is Not associated with any other group then it won't be present in groupAssociation table. So if the passed parameter is A then it should return A, B, B1, B2 and if passed parameter is B then query should return B, B1, B2.

. Please help...

Shamshad Ali

Post #1231469

Posted Wednesday, January 11, 2012 10:30 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 12, 2012 10:21 PM
Points: 526, Visits: 588

being busy in other tasks i did not get chance to see fix for this. Anyone help me in this. seems the post goes far from gurus for help.

Shamshad Ali.

Post #1234524

Posted Thursday, January 12, 2012 12:59 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 921, Visits: 3,815

;WITH Groups AS (
	SELECT GroupId = g.Id, g.GroupName, ga.ParentGroupId
	FROM dbo.[group] g 
	LEFT JOIN GroupAssociations ga on g.Id = ga.GroupId
)

, rCTE AS (
        SELECT [Level] = 1, GroupId, GroupName, ParentGroupId -- anchor part
        FROM Groups  
        WHERE ParentGroupId IS NULL
        UNION ALL 
        SELECT [Level] = [Level] + 1, tr.GroupId, tr.GroupName, tr.ParentGroupId -- recursive part
        FROM Groups tr 
        INNER JOIN rCTE lr ON lr.GroupId = tr.ParentGroupId
        WHERE lr.[Level] < 8
) 
SELECT *
FROM rCTE

The rCTE works like this:

The anchor part - the first select - picks up all rows which are top-level i.e. have no parent.

WHERE ParentGroupId IS NULL

The first iteration of the recursive part picks up rows which have, as their parent, rows from the anchor.

lr.GroupId = tr.ParentGroupId  (lastrow.GroupId = thisrow.ParentGroupId)

The second iteration picks up their "children" and so on.
On each iteration of the recursive part, rCTE lr contains the results of the last iteration - which is the anchor set of rows for the first iteration.

^{Low-hanging fruit picker and defender of the moggies}

For better assistance in answering your questions, please read this.

Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Post #1234550

Posted Thursday, January 12, 2012 10:26 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 12, 2012 10:21 PM
Points: 526, Visits: 588

Mr. 50, thanks for your time. Your given solution does not have any parameters and whichi is what i am looking for. If you read this whole post you will understand my query and will better reply with correct/requested solution.

Users will give parameters of GroupId in comma separated string and i have to return all subGroups incase there is no any entry in group association the earlier solution i have is not returning required results. Plz. review earlier post for better understanding. Thanks again.

Shamshad Ali.

Post #1235380

Posted Friday, January 13, 2012 12:18 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 921, Visits: 3,815

Since the query above resolves out children from parents, you first have to resolve the parents of the groups you want to pass in:

;WITH 
Groups AS (
	SELECT GroupId = g.Id, g.GroupName, ga.ParentGroupId
	FROM dbo.[group] g 
	LEFT JOIN GroupAssociations ga on g.Id = ga.GroupId
), 
Parents AS (
	SELECT GroupId, GroupName, ParentGroupId
	FROM Groups 
	WHERE GroupName in ('B2')
	UNION ALL
	SELECT g.GroupId, g.GroupName, g.ParentGroupId
	FROM Groups g
	INNER JOIN Parents lr ON lr.ParentGroupId = ga.GroupId
) SELECT * FROM Parents

This returns B2 (the group passed in), B (parent of B2), and A (parent of B). Now, you want all associations - upstream and downstream, so we use the output from this query as the anchor part of the generic rCTE I posted yesterday. You could stream the result from the rCTE "Parents" into a temp table and use it as a feed:

;WITH 
Groups AS (
	SELECT GroupId = g.Id, g.GroupName, ga.ParentGroupId
	FROM dbo.[group] g 
	LEFT JOIN GroupAssociations ga on g.Id = ga.GroupId
)
, rCTE AS (
        SELECT [Level] = 1, g.GroupId, g.GroupName, g.ParentGroupId -- anchor part
        FROM Groups g
        INNER JOIN #Parents p ON p.GroupId = g.GroupId  
        UNION ALL 
        SELECT [Level] = [Level] + 1, tr.GroupId, tr.GroupName, tr.ParentGroupId -- recursive part
        FROM Groups tr 
        INNER JOIN rCTE lr ON lr.GroupId = tr.ParentGroupId
        WHERE lr.[Level] < 8
) 
SELECT DISTINCT GroupId, GroupName
FROM rCTE

Alternatively, you could chain them all together like this:

;WITH 
Groups AS (
	SELECT GroupId = g.Id, g.GroupName, ga.ParentGroupId
	FROM dbo.[group] g 
	LEFT JOIN GroupAssociations ga on g.Id = ga.GroupId
), 
Parents AS (
	SELECT GroupId, GroupName, ParentGroupId--, ParentGroupName 
	FROM Groups 
	WHERE GroupName in ('B2')
	UNION ALL
	SELECT ga.GroupId, ga.GroupName, ga.ParentGroupId--, ga.ParentGroupName 
	FROM Groups ga
	INNER JOIN Parents lr ON lr.ParentGroupId = ga.GroupId
), 
rCTE AS (
        SELECT [Level] = 1, g.GroupId, g.GroupName, g.ParentGroupId -- anchor part
        FROM Groups g
        INNER JOIN Parents p ON p.GroupId = g.GroupId  
        UNION ALL 
        SELECT [Level] = [Level] + 1, tr.GroupId, tr.GroupName, tr.ParentGroupId -- recursive part
        FROM Groups tr 
        INNER JOIN rCTE lr ON lr.GroupId = tr.ParentGroupId
        WHERE lr.[Level] < 8
) 
SELECT DISTINCT GroupId, GroupName
FROM rCTE

Results:

GroupId	GroupName
1	A
2	B
3	B1
4	B2
5	B101

^{Low-hanging fruit picker and defender of the moggies}

For better assistance in answering your questions, please read this.

Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Post #1235399

« Prev Topic | Next Topic »

Add to briefcase

17 posts, Page 2 of 2

««1 2

Permissions

Expand / Collapse