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 = 2 order 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 details
- Permissions 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)