I use SQL Server, and though not pertinent here, Railo's CFML engine.I'm not great at SQL Joins, but I worked through this one and got the result set that I want. This particular SQL will only ever return 10 to 20 records, so even if it's a weak method, I'd probably never see a lot of grief from it since it's on an admin page to edit user profiles.However, obviously it's best to learn early to improve, before habits set in. It's conceivable, and likely, that I'll encounter a similar situation in the future where the potential size of the result could be several times larger.It's also possible that there's a faster way to retrieve the same results.The goal of this page is to list out the user details, and a list of roles along the side with checkboxes. That's easy to code. I'm not asking for help with that.This is the SQL that I came up with, and I'll explain my db layout below it.select r.roleID, r.roleTitle, pu.*, 'constant' as qc from roles r    left outer join (select u.*, p.roleID as xrole from permissions p         left join users u on p.userID = u.userID              and p.userID = 2) pu on pu.xrole = r.roleID         and pu.userID = 2order by len(username) desc(Both occurrences of the number 2 are merely to test, this will naturally be replaced with a variable in my .cfm page)I have three tables:Users - typical users table (UserID, Username, other personal detailsPermissions Table (UserID, RoleID)Permissions, likely obvious, links the other two tables. If a User doesn't have an entry matching their userID and the role they're trying to access, can't access the role.Roles (RoleID, RoleTitle)Notes- I'm not sure if I would naturally always get a row with userdata first, so I added an order by clause.- 'constant' as qc is added so that I have one column that has a reliable value so that I can always group by that (cfoutput grouping, not sql grouping, I then cfoutput-group by roleID for the checkboxes for roles)
Role-based access control query
sql;sql server
No, this s not the best way for several reasons:.First, with this CTE test data common to all three runs:with Users as ( select * from (values        (1,'Pieter')       ,(2,'Betty')       ,(3,'Robert')       ,(4,'Fred')       ,(5,'George')       ,(6,'Anne')   )users(UserID,Username)),Roles as ( select * from (values        (100,'Guest')       ,(101,'Public')       ,(  1,'Admin')       ,(102,'Editor')   )Roles(RoleID,RoleTitle)),Permissions as ( select * from (values        (1,101)       ,(2,101),(2,102)       ,(3,101),(1,102)       ,(4,101)       ,(5,100)       ,(6,1)       ,(7,101),(1,102)       ,(8,101)   )Permissions(UserID,RoleID))Your suggested SQL with results is:select r.roleID, r.roleTitle, pu.* from roles r    left outer join (select u.*, p.roleID as xrole from permissions p         left join users u on p.userID = u.userID              and p.userID = 2) pu on pu.xrole = r.roleID         and pu.userID = 2roleID      roleTitle UserID      Username xrole----------- --------- ----------- -------- -----------100         Guest     NULL        NULL     NULL101         Public    2           Betty    1011           Admin     NULL        NULL     NULL102         Editor    2           Betty    102Note that the same result is obtained if we tidy the SQL somewhat to this:select     r.roleID, r.roleTitle,     u.UserID, u.UsrName from permissions pjoin users       u on u.UserID = p.UserID                  and p.UserID = 2right join roles r on p.RoleID = r.RoleID;roleID      roleTitle UserID      Username----------- --------- ----------- --------100         Guest     NULL        NULL101         Public    2           Betty1           Admin     NULL        NULL102         Editor    2           BettyOrdering your joins can make your code much easier to read, and often avoid nested subqueries.Always list all field names (except possibly hen referencing a subquery which already lists them). This helsp to prevent your code from breaking with minor schema changes, such as additional fields or a re-ordering of the physical layout of the fields.