One-to-Many SELECT statement in a view
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 a开发者_如何学Pythonnd (0) if he is not
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
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
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
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.
My own solutions looks better.
精彩评论