InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 14:28:43 GMT20http://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspxSince the query above resolves out children from parents, you first have to resolve the parents of the groups you want to pass in:[code="sql"];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[/code]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:[code="sql"];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[/code]Alternatively, you could chain them all together like this:[code="sql"];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[/code]Results:[code="sql"]GroupId GroupName1 A2 B3 B14 B25 B101[/code]Fri, 13 Jan 2012 00:18:57 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspxMr. 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.Thu, 12 Jan 2012 22:26:24 GMTShamshad Alihttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspx[code="sql"];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[/code]The rCTE works like this: The anchor part - the first select - picks up all rows which are top-level i.e. have no parent.[code="sql"]WHERE ParentGroupId IS NULL[/code] The first iteration of the recursive part picks up rows which have, as their parent, rows from the anchor. [code="sql"]lr.GroupId = tr.ParentGroupId (lastrow.GroupId = thisrow.ParentGroupId)[/code] The second iteration picks up [i]their [/i]"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.Thu, 12 Jan 2012 00:59:34 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspxbeing 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.Wed, 11 Jan 2012 22:30:02 GMTShamshad Alihttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspxyes 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.[code="sql"]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[/code]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.[quote]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.[/quote]. Please help...Shamshad AliFri, 06 Jan 2012 07:48:20 GMTShamshad Alihttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspx[quote][b]Shamshad Ali (1/6/2012)[/b][hr]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 ....[/quote]Since you already have entities in the groupAssociation table which have no parent, shouldn't these be in the groupAssociation table also?Fri, 06 Jan 2012 04:41:49 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspxHello 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:[code="sql"]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'[/code]-- 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;[code="sql"];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[/code] 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.Fri, 06 Jan 2012 04:32:08 GMTShamshad Alihttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspxThank you very much for your help.my final function now looks as follows:[code="sql"]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 GroupIdreturnEND[/code]and I am using it here NO need to return as string:[code="sql"] 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 [/code]Shamshad Ali.Tue, 03 Jan 2012 22:53:35 GMTShamshad Alihttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspx>> 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. <<No. In RDBMS, we use scalars and tables, never a list. A function by definition returns a scalar value. No. 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: <<This is awful code. Good SQL programmers never use IDENTITY; the count of PHYSICAL insertion attempts has nothing to do with a valid logical model. Good SQL programmers seldom use BIGINT because in the real world seldom have a data SET with more elements than there are atoms in the Universe. Good SQL programmers know that the size of a data element is important, so oversized data element only invite garbage. Good SQL programmers have VERY few NULL-able columns in the entire schema. Good SQL programmers know that a table to have a key and that a key is a subset of attributes.What you have is called an adjacency list model of a hierarchy. It mimics non-relational pointer chains. Your code is how a COBOL Programmers write SQL; you even put the comma in the ront of the liens of code! That is an old 1950's punch card trick. CREATE TABLE Groups_Association(magic_generic_id INTEGER NOT NULL, group_id INTEGER NOT NULL, group_name VARCHAR(250), parent_group_id BIGINT, parent_group_id VARCHAR(250));You write with a lot of needless dialect. Did you know that T-SQL now has ANSI syntax for insertion? WE also use CAST() instead of the old Sybase CONVERT(), etc. >> I tried two functions to RETURN me Groups Idsthe first one take one single 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: <<Completely wrong approach to SQL. Without looking further, I know I will find loops, IF-then instead of CASE expressions, BIT flags, local variables, scratch tapes and decks of punch cards mimicked in SQL. They aklso write a lot of UDFs to mimic the procesreyual code they love. Today, bad SQL Programmers use CAST() to get back to COBOL strings for dates, and XML to avoid SQL completely and to get back to a hierarchical data model instead of sets. In short, 1950's COBOL procedural code instead of proper declarative SQL. Now I looked at the code and I was right. I am pretty good at bad SQL after three decades :)Passing a list of parameters to a stored procedure can be done by putting them into a string with a separator. I like to use the traditional comma. Let's assume that you have a whole table full of such parameter lists:CREATE TABLE InputStrings(keycol CHAR(10) NOT NULL PRIMARY KEY, input_string VARCHAR(255) NOT NULL);INSERT INTO InputStrings VALUES ('first', '12, 34, 567, 896'), ('second', '312, 534, 997, 896'), etc.This will be the table that gets the outputs, in the form of the original key column and one parameter per row.It makes life easier if the lists in the input strings start and end with a comma. You will need a table of sequential numbers -- a standard SQL programming trick, Now, the query, CREATE VIEW ParmList (keycol, place, parm)ASSELECT keycol, COUNT(S2.seq), -- reverse order CAST (SUBSTRING (I1.input_string FROM S1.seq FOR MIN(S2.seq) - S1.seq -1) AS INTEGER) FROM InputStrings AS I1, Series AS S1, Series AS S2 WHERE SUBSTRING (', ' + I1.input_string + ', ', S1.seq, 1) = ', ' AND SUBSTRING (', ' + I1.input_string + ', ', S2.seq, 1) = ', ' AND S1.seq < S2.seq GROUP BY I1.keycol, I1.input_string, S1.seq;This is ONE dialect free, portable SQL statement that does what you did with huge amounts of procedural code full of dialect. Now, back to the original question. Look up the Nested Sets model or get a copy of THINKING IN SETS from Amazon.com or a bookstores. What you described in your narrative is infinitely recuresive:Group B1 and B2 are part Group BGroup B1 is also part of Group AGroup A is part of Group BGroup B is part of Group ASee how B2 is also a part of A by being in B? And therefore it is part of itself, to an infinite depth! Or a A and B the same? This is the data version of an infinite loop. Tue, 03 Jan 2012 08:59:01 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspx[quote][b]Shamshad Ali (1/3/2012)[/b][hr]...why we are limiting it to lr.Level < 8?does that means we are drilling down to 7th level? and NOT till nth Level?...[/quote]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.Tue, 03 Jan 2012 07:45:01 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspxThanks, 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 timeShamshad Ali.Tue, 03 Jan 2012 07:22:19 GMTShamshad Alihttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspxTry 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:[code="sql"];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 rCTEGROUP BY GroupId, GroupNameORDER BY GroupName[/code]Tue, 03 Jan 2012 06:06:30 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspxMany 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 independentYour result GroupId Groups----------- -----------------269 B1270 B2271 B,B1,B2,A272 A,B1,B273 C274 Dthe 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) [code="sql"]GroupIds Groups----------- -----------------269,271 B1,B2,B270 B2271 B,B1,B2,A272 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 C274 D273,271 B1,B2,B,A,C274,272 B1,B2,B,A,D[/code]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. helpShamshad Ali.Tue, 03 Jan 2012 05:50:58 GMTShamshad Alihttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspxHaving some difficulty understanding what you're after, so here's my best guess.[code="sql"]BEGIN TRANCREATE 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 ALLSELECT 270, 'B2', 271, 'B' UNION ALLSELECT 269, 'B1', 272, 'A' UNION ALLSELECT 272, 'A', 271, 'B' UNION ALLSELECT 271, 'B', 272, 'A' UNION ALLSELECT 273, 'C', NULL, NULL UNION ALLSELECT 274, 'D', NULL, NULL--Actual querySELECT GroupId, GroupsFROM (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) aGROUP BY GroupId, GroupsROLLBACK[/code]This returns: -[code="plain"]GroupId Groups----------- -----------------269 B1270 B2271 B,B1,B2,A272 A,B1,B273 C274 D[/code]Tue, 03 Jan 2012 03:41:38 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspxPoint 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:[code="plain"]Msg 530, Level 16, State 1, Line 1The statement terminated. The maximum recursion 100 has been exhausted before statement completion.[/code]Tue, 03 Jan 2012 02:44:59 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspxHave you tried using [url=http://msdn.microsoft.com/en-us/library/ms186243.aspx]recursive CTE[/url]'s?Tue, 03 Jan 2012 02:41:26 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1228637-392-1.aspxI 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:[code="sql"]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]GOINSERT 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, NULLSELECT 274, 'D', NULL, NULL[/code]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 independentWe 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 Idsthe first one take one single groupId parameter and return its all sub groups including itselfthe second one take parameters of group list (varchar) to return all subgroups and their subgroups but not working with nth subgroup level:[code="sql"]-- First FunctionCREATE 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 @strEND[/code]Following two are helper functions[code="sql"]-- helper functionsCREATE 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 ENDGOCREATE 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 [/code]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.Sat, 31 Dec 2011 03:30:36 GMTShamshad Ali