DECLARE @__role_0 nvarchar(450) = N'admin';

SELECT [u].[ID], [u].[FIRST_NAME], [u].[LAST_NAME], [u].[PASSWORD_HASH], [u].[USER_NAME], [t].[USER_ID], [t].[ROLE_ID], [t].[ID], [t].[CREATION_DATE], [t].[ROLE]
FROM [Users] AS [u]
LEFT JOIN (
    SELECT [u1].[USER_ID], [u1].[ROLE_ID], [r0].[ID], [r0].[CREATION_DATE], [r0].[ROLE]
    FROM [UserRoles] AS [u1]
    INNER JOIN [Roles] AS [r0] ON [u1].[ROLE_ID] = [r0].[ID]
) AS [t] ON [u].[ID] = [t].[USER_ID]
WHERE EXISTS (
    SELECT 1
    FROM [UserRoles] AS [u0]
    INNER JOIN [Roles] AS [r] ON [u0].[ROLE_ID] = [r].[ID]
    WHERE [u].[ID] = [u0].[USER_ID] AND [r].[ROLE] = @__role_0)
ORDER BY [u].[ID], [t].[USER_ID], [t].[ROLE_ID]