I have three tables, Users, Roles, and a junction UserRoles.
Users UserRoles Roles
======= ============= =========
UserId UserId RoleId
Username RoleId Role
Firstname Date
My tables are joined like this:
Users.UserId -> UserRoles.UserId
Roles.RoleId -> UserRoles.RoleId
Basically, what I am looking for is an simple example that shows:
I managed to make a simple select on Roles for a specific Username like so:
SELECT
dbo.Roles.Role
FROM dbo.Roles
INNER JOIN dbo.UserRoles
ON dbo.Roles.RoleId = dbo.UserRoles.RoleId
INNER JOIN dbo.Users
ON dbo.UserRoles.UserId = dbo.Users.UserId
WHERE (Username = @Username)
To INSERT in UserRoles a UserId and a RoleId for a specific Username with a specific Role, you need to redesign your tables such that the User table has a foreign key to the Roles tables as follows:
Users UserRoles Roles
======= ============= =========
UserId UserId RoleId
Username RoleId Role
Firstname Date
RoleId
Then insert as follows:
insert into dbo.UserRoles (UserId, RoleId, [Date])
select
u.UserId,
r.RoleId,
getdate() as [Date]
from dbo.Users u
inner join dbo.Roles r
on r.RoleId = u.RoleId
and u.Username = @Username
To delete a role for a specific Username with the above schema:
delete r
from dbo.Roles r
inner join dbo.User u
on r.RoleId = u.Roleid
where u.Username = @Username
EDIT: Another solution suggested by @sidux
DELETE ur
FROM dbo.UserRoles ur
JOIN dbo.Roles r
ON r.RoleId = ur.RoleId
INNER JOIN dbo.Users u
ON ur.UserId = u.UserId
WHERE (Username = @Username);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments