Due to the nature of my query I have to use SQL rather than aggregates, within the SQL query I am using a Union as below
Query 1 - Users who have a role in the application (users may have multiple roles), although the roles run across 2 espaces.
Union
Query 2 - Users who dont have a role in the application.
The difficulty is the users in query 1 also appear in query 2 due to a common (internal role). So I need to exclude those users in query 1 from query 2.
Can anyone advise how I can achieve this using SQL?
Screenshot of SQL also attached.
I don't think you need a union to do that.
I would suggest to try the following:
SELECT
{User}.[Id],
{UserExt}.[NameForPickLists],
{User}.[Email],
{FT_UnionLeadRoleRequest}.[UserRoleStatusId],
STUFF((
SELECT ',' + {Role}.[Description]
FROM {Role}
inner join {User_Role} on {User_Role}.[Role_Id] = {Role}.[Id]
inner JOIN {Espace} ON {Role}.[Espace_Id] = {Espace}.[Id]
where ({User_Role}.[User_Id] = {User}.[Id])
AND ({Espace}.[Name] = 'FacilityTime_Theme')
FOR XML PATH('')
), 1, 1, '')
FROM {User}
Inner JOIN {UserExt} ON {User}.[Id] = {UserExt}.[UserId]
WHERE ({User}.[Is_Active] = 1)
And ({UserExt}.[NameForPickLists] like '%' + @SearchUser + '%' or {User}.[Email] like '%' + @SearchUser + '%')
And (@SearchRoleId = '' OR EXISTS (SELECT 1 FROM {User_Role} WHERE {User_Role}.[User_Id] = {User}.[Id] AND {User_Role}.[Role_Id] = @SearchRoleId))
In this way, you get the users and the concatenated roles they have coming from Facility_Theme (users with no roles will also appear) and you can search by user or by roles the user has.
Hope it helps,
João
Hi Lee,
Something happened with your screenshot, it appears "Processing upload...".
Can you upload the screenshot again, please?
Cheers,
João Marques wrote:
Hi Joao, I have tried to upload as a screenshot and as a Notepad++ doc, neither have worked hence the "processing upload". Managed to copy across to Word and attach if that helps.
I need to exclude the users in the first query from appearing in the 2nd query.
Thank you kindly Joao for your quick and comprehensive response........works a treat.
Think I was over complicating the problem!!!