Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Recusion on Group and their subGroups- how can I solve this problem? Expand / Collapse
Author
Message
Posted Friday, January 6, 2012 4:32 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 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 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 6, 2012 4:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 1,037, Visits: 6,946
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 6, 2012 7:48 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, 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



Post #1231469
Posted Wednesday, January 11, 2012 10:30 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 1,037, Visits: 6,946
;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

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 1,037, Visits: 6,946
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 ««12

Permissions Expand / Collapse