Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Recusion on Group and their subGroups- how can I solve this problem? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, January 06, 2012 4:32 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Friday, September 06, 2013 5:54 AM Points: 526, Visits: 590
 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 ALLSELECT 4, 'B2', 2, 'B' UNION ALLSELECT 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 rCTEGROUP BY GroupId-- , GroupNameORDER BY GroupId` the above code return correct data with B i.e B, B1, B2but 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 @ 1:08 PM Points: 959, Visits: 4,560
 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: Friday, September 06, 2013 5:54 AM Points: 526, Visits: 590
 yes you are right Mr. 500here 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 allselect 2, 'B' union allselect 3, 'B1' union allselect 4, 'B2' union allselect 5, 'B101' union all select 6, 'C' union allselect 7, 'D' union allselect 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 ALLSELECT 4, 'B2', 2, 'B' UNION ALLSELECT 2, 'B', 1, 'A' UNION ALL--SELECT 1, 'A', null, null UNION ALLSELECT 5, 'B101', 3, 'B1' -- UNION ALL--SELECT 6, 'C', NULL, NULL UNION ALL--SELECT 7, 'D', NULL, NULLselect * 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 rCTEGROUP BY GroupId-- , GroupNameORDER 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: Friday, September 06, 2013 5:54 AM Points: 526, Visits: 590
 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 @ 1:08 PM Points: 959, Visits: 4,560
 `;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: Friday, September 06, 2013 5:54 AM Points: 526, Visits: 590
 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 @ 1:08 PM Points: 959, Visits: 4,560
 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, GroupNameFROM 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, GroupNameFROM rCTE`Results:`GroupId GroupName1 A2 B3 B14 B25 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

 Permissions