December 31, 2011 at 3:30 am
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.
January 3, 2012 at 2:41 am
This was removed by the editor as SPAM
January 3, 2012 at 2:45 am
This was removed by the editor as SPAM
January 3, 2012 at 3:41 am
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
January 3, 2012 at 5:50 am
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.
January 3, 2012 at 6:06 am
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
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 3, 2012 at 7:22 am
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.
January 3, 2012 at 7:45 am
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.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 3, 2012 at 10:53 pm
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.
January 6, 2012 at 4:32 am
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.
January 6, 2012 at 4:41 am
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?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 6, 2012 at 7:48 am
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
January 11, 2012 at 10:30 pm
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.
January 12, 2012 at 12:59 am
;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.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 12, 2012 at 10:26 pm
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.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply