I'm having trouble figuring this query out, maybe you guys can help me.
I'm adding a user administration module to an existing system, where an administrator can view and modify users' access rights to particular sections.
We got three tables: Users, Sections, and UserSectionRights. UserSectionRights has a foriegn key to User, a Foreign key to userSectionRights, and an integer representing the access level for that user/section combination.
For this admin module, i would like to get a table that looks like this:
Code:
| Section 1 Access Level | Section 2 Access Level | ... <more columns for however many sections>
-----------------------------------------------------------------------------------------------------------------------
UserName1 | | |
UserName2 | | |
UserName3 | | |
UserName4 | | |
UserName5 | | |
...etc...
If I use joins, i end up getting a line for each user/section combination, as opposed to having all the combinations together. That's gonna be what I'll end up dealing with, if I can't figure this out.
I don't think subqueries are an option, because i'd like this to be dynamic, so that this query will still work when new sections are added. As I see it, i'd have to hard-code a subquery for each section.
Any ideas? hopefully i've explained things clearly! :-)