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 Saturday, December 31, 2011 3:30 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
I have to pass a list of groups to a function which return those groups and their sub-groups till nth level into all those groups returned. I think we can't do this by single query and in my case I have to use temp tables, if else & while loops which I tried in following code.
I have following data for providing as an example:

CREATE TABLE [dbo].[groupsAssociation](
[Id] [int] NOT NULL,
[GroupId] [int] IDENTITY(1,1) NOT NULL,
[GroupName] [varchar](250) NULL,
[ParentGroupId] [bigint] NULL,
[ParentGroupName] [varchar](250) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[groupsAssociation]
([GroupId]
,[GroupName]
,[ParentGroupId]
,[ParentGroupName])
SELECT 269, 'B1', 271, 'B'
SELECT 270, 'B2', 271, 'B'
SELECT 269, 'B1', 272, 'A'
SELECT 272, 'A', 271, 'B'
SELECT 271, 'B', 272, 'A'
SELECT 273, 'C', NULL, NULL
SELECT 274, 'D', NULL, NULL

Group B1(269) and B2(270) are part Group B(271)
Group B1(270) is also part of Group A(272)
Group A(272) is part of Group B(271)
Group B(271) is part of Group A(272)
Group C(273) and Group D(274) are independent

We are following Active directory groups and synchronizing data, I am stuck here how if I have to pull the users of these groups for example I have to pull users that are part of Group B then it will return me all the group Ids (B1, B2, B, A) same as if i pull Group A users then it shold also return me all users of all groups under this groupsAssociation table (B1, B2, B, A), if i say to pull users of group (A, C, D) then it should return me (B1, B2, B, A, C, D)

I tried two functions to return me Groups Ids
the first one take one single groupId parameter and return its all sub groups including itself
the second one take parameters of group list (varchar) to return all subgroups and their subgroups but not working with nth subgroup level:




-- First Function
CREATE FUNCTION [dbo].[GetAllSubGroups]
(
-- Add the parameters for the function here
@parentGroupId varchar(50)
)
RETURNS varchar(4000)
AS
BEGIN

DECLARE @finalGroups varchar(8000)
DECLARE @parentGroupIdCopy varchar(50)
DECLARE @Pos int
DECLARE @Token varchar(50)
DECLARE @subGroups varchar(8000)
DECLARE @ShouldDo bit
DECLARE @cnt int
set @cnt = 0
--set @parentGroupId = '62'

set @parentGroupIdCopy = @parentGroupId

SET @parentGroupId = LTRIM(RTRIM(@parentGroupId))+ '#'
SET @Pos = CHARINDEX('#', @parentGroupId, 1)
set @Token = ''
set @ShouldDo = 1
set @finalGroups = ''
-----------------------------------
DECLARE @TempTable table ([GroupId] int NOT NULL)
DECLARE @RemainingGroups table ([GroupId] int NOT NULL)
IF REPLACE(@parentGroupId, '#', '') <> ''
BEGIN
insert into @TempTable ([GroupId])
select convert(int, @parentGroupIdCopy)
-- select Id, groups from @TempTable
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@parentGroupId, @Pos - 1)))
IF @Token <> ''
BEGIN
set @ShouldDo = 1 --reset it here.
WHILE @ShouldDo > 0
BEGIN
set @subGroups = [dbo].[GetSubGroups] (@Token)
set @subGroups = LTRIM(RTRIM(@subGroups))
-- Add groups one by one here
set @cnt = @cnt + 1

insert into @TempTable ([GroupId])
select convert(int, ColumnData) from fn_CSVToTable(@subGroups) where ColumnData not in (select [GroupId] from @TempTable)
--insert into @rtnTable select * from @TempTable
--if (@cnt = 5)
-- return
--set @subGroups =
if Len(IsNull(@subGroups,'')) > 0
begin
set @finalGroups = @subGroups + ',' + @finalGroups
-- exclude those groups that are already we traversed/retrived
insert into @RemainingGroups ([GroupId])
select convert(int, ColumnData) from fn_CSVToTable(@subGroups) where ColumnData not in (select [GroupId] from @TempTable)
set @subGroups = (
select Left(IGroups,Len(IGroups)-1) [IGroups] from
(SELECT TOP 1
( SELECT convert(varchar (10), GroupId) + ','
FROM @RemainingGroups d2 ORDER BY 1
FOR XML PATH('') ) AS [IGroups]
FROM @RemainingGroups) as d1)
set @Token = [dbo].[GetSubGroups] (@subGroups)
--return @subGroups
end
else
begin
set @finalGroups = IsNull(@Token,'') + ',' + @finalGroups
SET @ShouldDo = 0
end
END
END

SET @parentGroupId = RIGHT(@parentGroupId, LEN(@parentGroupId) - @Pos)
SET @Pos = CHARINDEX('#', @parentGroupId, 1)
END
END

set @finalGroups = @finalGroups + LTRIM(RTRIM(@parentGroupIdCopy))

set @finalGroups = (
select Left(IGroups,Len(IGroups)-1) [IGroups] from
(SELECT TOP 1
( SELECT distinct convert(varchar (10), GroupId) + ','
FROM @TempTable d2 ORDER BY 1
FOR XML PATH('') ) AS [IGroups]
FROM @TempTable) as d1)

RETURN @finalGroups

END
GO
-- Second function
Create FUNCTION [dbo].[GetAllSubGroupsByList]
(
-- Add the parameters for the function here
@GroupIdsList varchar(500)
)
RETURNS varchar(4000)
AS
BEGIN
-- select dbo.[GetAllSubGroupsByList]('271')

Declare @str varchar(4000)
DECLARE @GroupsTable table ([GroupId] int NOT NULL)
insert into @GroupsTable ([GroupId])
select Id from [Group] where id in (select convert(int, ColumnData) from fn_CSVToTable(@GroupIdsList))
DECLARE @GroupsTableB table ([GroupIds] varchar(500))
insert into @GroupsTableB ([GroupIds])
select dbo.GetAllSubGroups(GroupId) from @GroupsTable
-- set @str = (
--SELECT DISTINCT STUFF( (SELECT distinct convert(varchar (4000),GroupIds) + ',' from @GroupsTableB
-- FOR XML PATH('')),1,1,'') as GroupIds
--FROM @GroupsTableB)
set @str = (
select Left(IGroups,Len(IGroups)-1) [IGroups] from
(SELECT TOP 1
( SELECT distinct convert(varchar (4000), GroupIds) + ','
FROM @GroupsTableB d2 ORDER BY 1
FOR XML PATH('') ) AS [IGroups]
FROM @GroupsTableB) as d1)

return @str
END


Following two are helper functions

-- helper functions
CREATE FUNCTION [dbo].[GetSubGroups]
(
-- Add the parameters for the function here
@parentGroupIdList nvarchar(500)
)
RETURNS nvarchar(4000)
AS
BEGIN

DECLARE @allSubGroups nvarchar(4000)

SELECT @allSubGroups =
(CASE WHEN @allSubGroups IS NULL
THEN Convert(nvarchar(10),GroupId)
ELSE
@allSubGroups + ',' + Convert(nvarchar(10),GroupId)
END)

FROM dbo.GroupsAssociation where ParentGroupId IN (SELECT ColumnData FROM [dbo].[fn_CSVToTable] (@parentGroupIdList))

RETURN @allSubGroups

END
GO
CREATE Function [dbo].[fn_CSVToTable] (@CSVList Varchar(3000))
Returns @Table Table (ColumnData int)
As
Begin
If right(@CSVList, 1) <> ','
Select @CSVList = @CSVList + ','

Declare @Pos Smallint,
@OldPos Smallint
Select @Pos = 1,
@OldPos = 1

While @Pos < Len(@CSVList)
Begin
Select @Pos = CharIndex(',', @CSVList, @OldPos)
Insert into @Table
Select cast(LTrim(RTrim(SubString(@CSVList, @OldPos, @Pos - @OldPos))) as int) Col001
Select @OldPos = @Pos + 1
End

Return
End



My requirement is user will give a list of coma separated Group Ids i.e. (273, 272) then it will return all groups and their subgroups till Nth level and return those groups, then I will be able to pass those groups to another table (GroupUsers) to pull their distinct emailAddresses and send them email. which part i am already doing but the main problem is recursion with Groups, A group would be part of B and B group could be part of A.


Shamshad Ali.



Post #1228637
Posted Tuesday, January 3, 2012 2:41 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 4,546, Visits: 6,036
Have you tried using recursive CTE's?

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1229099
Posted Tuesday, January 3, 2012 2:44 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 4,546, Visits: 6,036
Point of note - using the sample data you provided, a circular reference is generated (e.g. Group 271 has ParentGroup 272, and Group 272 has ParentGroup 271), which will cause an eventual error:
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1229100
Posted Tuesday, January 3, 2012 3:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 2,458, Visits: 7,947
Having some difficulty understanding what you're after, so here's my best guess.

BEGIN TRAN

CREATE TABLE [dbo].[groupsAssociation](
[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].[groupsAssociation]
([GroupId]
,[GroupName]
,[ParentGroupId]
,[ParentGroupName])
SELECT 269, 'B1', 271, 'B' UNION ALL
SELECT 270, 'B2', 271, 'B' UNION ALL
SELECT 269, 'B1', 272, 'A' UNION ALL
SELECT 272, 'A', 271, 'B' UNION ALL
SELECT 271, 'B', 272, 'A' UNION ALL
SELECT 273, 'C', NULL, NULL UNION ALL
SELECT 274, 'D', NULL, NULL

--Actual query
SELECT GroupId, Groups
FROM (SELECT GroupId,
GroupName + ISNULL(',' + STUFF((SELECT ',' + GroupName
FROM (SELECT children.GroupName, nodes.Id
FROM [groupsAssociation] nodes
--Get child ID
OUTER APPLY (SELECT [ParentGroupId], [GroupId] AS childid, [GroupName]
FROM [groupsAssociation] st
WHERE nodes.[GroupId] = st.[ParentGroupId]) children) t2
WHERE t2.Id = t1.Id
FOR XML PATH('')), 1, 1, ''),'') AS Groups
FROM [groupsAssociation] t1) a
GROUP BY GroupId, Groups

ROLLBACK

This returns: -
GroupId     Groups
----------- -----------------
269 B1
270 B2
271 B,B1,B2,A
272 A,B1,B
273 C
274 D



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn
Post #1229124
Posted Tuesday, January 3, 2012 5:50 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
Many thanks that someone tried to resolve my problem. I have been looking for replies since i posted my question. The result is incorrect because when A is part of B and B is part of A so in this case if user either provide A group then all of B and its subGroups should be returned, same as if user provide B group as parameter then I have to traverse all subGroups of B and A in this case. I mean there is recursion but it won't break in loop like recursive CTP returned error as mentioned by one above.

Group B1(269) and B2(270) are part Group B(271)
Group B1(269) is also part of Group A(272)
Group A(272) is part of Group B(271)
Group B(271) is part of Group A(272)
Group C(273) and Group D(274) are independent




Your result
GroupId Groups
----------- -----------------
269 B1
270 B2
271 B,B1,B2,A
272 A,B1,B
273 C
274 D


the parameters are GroupId list comma separated provided by users as follows: and I have to put groupIds and their sub-group Ids till nth level (Does Not fail like CTE recusion cos its NOT a tree, its grouping of users and groups)


GroupIds Groups
----------- -----------------
269,271 B1,B2,B
270 B2
271 B,B1,B2,A
272 B,B1,B2,A (because B is part of A and A is part of B so all groups under B are part of A)
273 C
274 D
273,271 B1,B2,B,A,C
274,272 B1,B2,B,A,D

The function or SP will get a list of groupIds and i have to look their subgroups and groups till there is no any sub-groups in given groups till nth level and return list of groupIds. Further, i will then pass this list to groupusers table to pull users finally.

Hope you understand now. I think we can't solve it through single query yet. Plz. help

Shamshad Ali.



Post #1229187
Posted Tuesday, January 3, 2012 6:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 1,086, Visits: 7,979
Try this query, using different values of Groupname. If it works, then we'll pivot the results using FOR XML PATH to put them into a comma-delimited list:

;WITH rCTE AS (
SELECT Level = 1, Id, GroupId, GroupName, ParentGroupId, ParentGroupName
FROM dbo.groupsAssociation
WHERE [GroupName] IN ('A') --('A','C','D')
UNION ALL
SELECT Level = Level + 1, tr.Id, tr.GroupId, tr.GroupName, tr.ParentGroupId, tr.ParentGroupName
FROM dbo.groupsAssociation 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 GroupName




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 #1229200
Posted Tuesday, January 3, 2012 7:22 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
Thanks, the given code works with sample code now. I have a question, why we are limiting it to lr.Level < 8?
does that means we are drilling down to 7th level? and NOT till nth Level?

I am going to try this query with some real data and hope it won't make the performance issue too.
Plus, I suspect when moving rows into coma separated values for a large result it makes a big performance problem. query takes time when doing FOR XML PATH('')

Thanks agian for your time



Shamshad Ali.



Post #1229246
Posted Tuesday, January 3, 2012 7:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 1,086, Visits: 7,979
Shamshad Ali (1/3/2012)
...why we are limiting it to lr.Level < 8?
does that means we are drilling down to 7th level? and NOT till nth Level?...

Where A is a member of B, and B is a member of A, as it is with your data, you require a sensible limit to the number of hierchical levels you wish to resolve. Without it your query won't finish. If the limiter is set too large, your result set will consist of many repeats of the same resolved hierarchy. If the limiter is set too low, you may lose results. It's up to you to determine what value to use.



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 #1229268
Posted Tuesday, January 3, 2012 10:53 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
Thank you very much for your help.

my final function now looks as follows:
Alter FUNCTION [dbo].[GetAllSubGroupsByList]           
(
-- Add the parameters for the function here
@GroupIdsList varchar(500)
)
RETURNS @Table Table (GroupId int)
AS
BEGIN
Declare @str varchar(500)
;WITH rCTE AS (
SELECT Level = 1, Id, GroupId, GroupName --, ParentGroupId, ParentGroupName
FROM dbo.groupsAssociation
-- WHERE [GroupName] IN ('A') --('A','C','D')
WHERE [GroupId] IN (Select ColumnData from [dbo].[fn_CSVToTable](@GroupIdsList))
UNION ALL
SELECT Level = Level + 1, tr.Id, tr.GroupId, tr.GroupName --, tr.ParentGroupId, tr.ParentGroupName
FROM dbo.groupsAssociation tr
INNER JOIN rCTE lr ON lr.GroupId = tr.ParentGroupId
WHERE lr.Level < 8
)
insert into @Table(GroupId)
SELECT GroupId FROM rCTE GROUP BY GroupId
return
END


and I am using it here NO need to return as string:

 SELECT  
IsNull(U.Id,0) as CustomUserUserId,
UP.ScreenName as CustomUserScreenName,
UP.EmailAddress as CustomUserEmailAddress,
UP.FirstName as CustomUserFirstName,
UP.LastName as CustomUserLastName,
U.TenantId as CustomUserTenantId,
T.TenantName as CustomUserTenantName,
IsNull(UG.GroupId ,0) as CustomUserGroupId,
IsNull(G.Name,'''') as CustomUserGroupName
FROM
UserProfile UP
inner join Users U on U.UserProfileId = UP.Id
inner join Tenant T on U.TenantId = T.Id
inner join UserGroups UG on t.Id = UG.TenantId and UG.UserId = U.Id
inner join [Group] G on UG.GroupId = G.Id
where U.IsActive = 1 and U.IsDeleted = 0 and UP.IsActive = 1 and UP.IsDeleted = 0 and UG.IsActive = 1 and U.UserTypeId != 4 and
UG.GroupId in (select GroupId from dbo.[GetAllSubGroupsByList](@listOfIds))
Order By U.Id

Shamshad Ali.



Post #1229672
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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse