SQL Group By and Having clause and exists clause

Question

These queries fetch records from multiple tables(AspNetUsers,AspNetUserRoles & AspNetRoles). The records will include only those users which have multiple Roles. I am looking for reasons why 1st query works and the latter did not. Any help would be appreciated.

Query 1:

SELECT 
    U.Id,
    U.UserName
    ,R.Id
    ,R.Name AS RoleName
FROM AspNetUsers  AS U
JOIN AspNetUserRoles UR
    ON U.Id  = UR.UserId
JOIN AspNetRoles AS R
    ON R.Id = UR.RoleId
WHERE EXISTS (
        SELECT UserId,
            COUNT() AS NumberofRoles
            FROM AspNetUserRoles
            GROUP BY UserId
            HAVING COUNT() > 1)   

Query 2:(Only work if I remove R.Id & R.Name Otherwise it is not working)

SELECT 
    U.Id,
    U.UserName
    ,R.Id
    ,R.Name AS RoleName
FROM AspNetUsers  AS U
JOIN AspNetUserRoles UR
    ON U.Id  = UR.UserId
JOIN AspNetRoles AS R
    ON R.Id = UR.RoleId
GROUP BY U.Id,U.UserName

The table diagram is attached for better clarity.

Answer 1

The second query doesn't woek because you haven't defined anything known as R.

Answer 2

try this :

SELECT 
    U.Id,
    U.UserName
    ,R.Id
    ,R.Name AS RoleName
FROM AspNetUsers  AS U
JOIN AspNetUserRoles UR
    ON U.Id  = UR.UserId
JOIN AspNetRoles AS R
    ON R.Id = UR.RoleId
GROUP BY U.Id,U.UserName,R.Id,R.Name

Answer 3

This would be the correct way to fetch records that have multiple Roles.

SELECT 
    U.Id,
    U.UserName
    ,R.Id AS RoleID
    ,R.Name AS RoleName
FROM AspNetUsers  AS U
JOIN AspNetUserRoles UR
    ON U.Id  = UR.UserId
JOIN AspNetRoles AS R
    ON R.Id = UR.RoleId
WHERE U.Id IN (SELECT UserId FROM AspNetUserRoles GROUP BY UserId HAVING COUNT(*) > 1)