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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 3,902, Visits: 5,073
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 3,902, Visits: 5,073
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: Yesterday @ 8:00 AM
Points: 2,433, Visits: 7,501
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 | Blog coming soon (for sufficiently large values of "soon" )!
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 @ 6:19 AM
Points: 1,075, Visits: 6,440
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 @ 6:19 AM
Points: 1,075, Visits: 6,440
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 8:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 1,945, Visits: 2,885
>> 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 Ids
the 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)
AS
SELECT 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 B
Group B1 is also part of Group A
Group A is part of Group B
Group B is part of Group A

See 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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1229333
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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse