GroupBy with Left Join duplicates records

Question

I'm trying to create query for Users with Roles. To do this I need to join User table and UserRole table. User might have multiple roles so I need to use Group By. Here what I have so far

SELECT 
    [user].Id,
    [user].Email,
    CONCAT([user].Firstname, ' ', [user].Lastname) AS [Name],
    IIF(userRole.roleId = 1, 1, 0) AS IsAgent,
    IIF(userRole.roleId = 2, 1, 0) AS IsManager,
    IIF(userRole.roleId = 3, 1, 0) AS IsAdmin,
    [user].IsActive
FROM 
    Access.[User] [user]
    LEFT JOIN Access.UserRole userRole ON userRole.UserId = [user].Id
GROUP BY 
    [user].Id, 
    [user].Email,
    [user].IsActive,
    userRole.roleId,
    [user].Firstname,
    [user].Lastname

But unfortunately it returns duplicates for the user with multiple roles:

So I've created another query (which works fine), but I'm not sure if this is correct way to solve this problem:

SELECT 
    [user].Id,
    [user].Email,
    CONCAT([user].Firstname, ' ', [user].Lastname) AS [Name],
    IIF(EXISTS (SELECT TOP 1 1 
                FROM Access.UserRole userRole
                WHERE userRole.UserId = [user].Id AND userRole.RoleId = 1), 1, 0) AS IsAgent,
    IIF(EXISTS (SELECT TOP 1 1 
                FROM Access.UserRole userRole
                WHERE userRole.UserId = [user].Id AND userRole.RoleId = 2), 1, 0) AS IsManager,
    IIF(EXISTS (SELECT TOP 1 1 
                FROM Access.UserRole userRole
                WHERE userRole.UserId = [user].Id AND userRole.RoleId = 3), 1, 0) AS IsAdmin,
    [user].IsActive
FROM 
    Access.[User] [user]
GROUP BY 
    [user].Id, 
    [user].Email,
    [user].IsActive,
    [user].Firstname,
    [user].Lastname

Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. [ask] Pause work on the overall goal, **chop code to the 1st expression not giving what you expect & say what you expect & why.** — philipxy, Oct 04 '20 at 12:50

score 1 · Accepted Answer · answered Oct 04 '20 at 12:50

Use aggregation function!:

SELECT [user].Id, [user].Email, CONCAT([user].Firstname, ' ', [user].Lastname) AS [Name],
       MAX(IIF(userRole.roleId = 1, 1, 0)) AS IsAgent,
       MAX(IIF(userRole.roleId = 2, 1, 0)) AS IsManager,
       MAX(IIF(userRole.roleId = 3, 1, 0)) AS IsAdmin,
       [user].IsActive
FROM Access.[User] [user] LEFT JOIN
     Access.UserRole userRole
     ON userRole.UserId = [user].Id
GROUP BY [user].Id,  [user].Email, [user].IsActive,
         [user].Firstname, [user].Lastname;

Note that I also removed userRole.roleId from the GROUP BY.

score -1 · Answer 2 · answered Oct 04 '20 at 13:17

-1

You are actually looking to "PIVOT" your result. See examples here: Convert Rows to columns using 'Pivot' in SQL Server

or google for more :)

answered Oct 04 '20 at 13:17

Dima G

1,945
18
22

GroupBy with Left Join duplicates records

2 Answers2