One-to-Many SELECT statement in a view

Question

There are 3 tables in a database:

Users (UserID, UserName), Roles(RoleID, RoleName) and UserRoles(UserID, RoleID)

How to create a view on Users table which will have IsAdmin column, here is a mockup:

CREATE VIEW UsersView AS
    SELECT
        u.UserID,
        u.UserName,
        CASE WHEN ur.RoleID IS NULL THEN (0) ELSE (1) END AS IsAdmin
    FROM Users AS u
        LEFT JOIN Roles AS r ON r.RoleName = N'Admins'
        LEFT JOIN UserRoles AS ur ON  ur.UserID = u.UserID
                                  AND ur.RoleID = r.RoleID

IsAdmin should be (1) if user is in "Admins" user role and (0) if he is not

Answer 1

try this

Here is another way...but I do like the EXISTS version that Charles Bretana posted better

CREATE VIEW UsersView AS
SELECT UserID,UserName, MAX(IsAdmin) as IsAdmin
FROM(SELECT
    u.UserID,
    u.UserName,
    CASE r.RoleName when 'Admins' then 1 else 0 end AS IsAdmin
FROM Users AS u
    LEFT JOIN UserRoles AS ur ON ur.UserID = u.UserID
    LEFT JOIN Roles r on ur.RoleID = r.RoleID) x
    GROUP BY UserID,UserName

Answer 2

try:

  CREATE VIEW UsersView AS 
    SELECT 
        u.UserID, 
        u.UserName, 
        Case When Exists
           (Select * from userRoles ur
                Join Roles r On r.RoleId = ur.Roleid
            Where ur.userId = u.UserId
               And r.RoleName = '"Admins') 
          Then 1 Else 0 End IsAdmin 
    FROM Users u 

Answer 3

Add another column to your Roles table, isAdmin, and set it to true only for the Admin role. Then, in views and such, check for the isAdmin marker in the where clause.

Answer 4

This approach worked. Take notice of how trivial it is to add new role checks.

Code

Declare @Users Table(UserID Int, UserName VarChar(256))
Declare @Roles Table(RoleID Int, RoleName VarChar(256))
Declare @UserRoles Table(UserID Int, RoleID Int)

Insert Into @Roles Select 1, 'Admins'
Insert Into @Roles Select 2, 'Role2'
Insert Into @Roles Select 3, 'Role3'
Insert Into @Roles Select 4, 'Genius'

Insert Into @Users Select 1, 'Phil'
Insert Into @UserRoles Select 1, 1
Insert Into @UserRoles Select 1, 2
Insert Into @UserRoles Select 1, 3
Insert Into @UserRoles Select 1, 4

Insert Into @Users Select 2, 'Jim'
Insert Into @UserRoles Select 2, 2
Insert Into @UserRoles Select 2, 3

Insert Into @Users Select 3, 'Susan'
Insert Into @UserRoles Select 3, 1
Insert Into @UserRoles Select 3, 2
Insert Into @UserRoles Select 3, 3


Select UserID,
       UserName,
       Cast([Admins] As Bit) As IsAdmin,
       Cast([Genius] As Bit) As IsGenius
From (
    Select  Users.UserID,
            Users.UserName,
            Roles.RoleName
    From @Users As Users
        Left Join @UserRoles As UserRoles On UserRoles.UserID = Users.UserID
        Left Join @Roles As Roles On UserRoles.RoleID = Roles.RoleID
) As Data
Pivot (
    Count(RoleName) For RoleName In (
        [Admins], [Genius]
    )
) As Result

Result

UserID  UserName IsAdmin IsGenius
2       Jim      0       0
1       Phil     1       1
3       Susan    1       0