SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Shamshad Ali
Shamshad Ali
Right there with Babe
Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)

Group: General Forum Members
Points: 720 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 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.



ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2125 Visits: 10392
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
Shamshad Ali
Shamshad Ali
Right there with Babe
Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)

Group: General Forum Members
Points: 720 Visits: 590
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



Shamshad Ali
Shamshad Ali
Right there with Babe
Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)

Group: General Forum Members
Points: 720 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.



ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2125 Visits: 10392

;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
Shamshad Ali
Shamshad Ali
Right there with Babe
Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)

Group: General Forum Members
Points: 720 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.



ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2125 Visits: 10392
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search