Results 1 to 4 of 4
Thread: Returning values as field names
Mar 1, 2011, 07:59 #1
Returning values as field names
This isn't the easiest problem to describe but I'll do my best...
I have 3 tables, with the field names in brackets;
UserAccounts (ID, UserNick, Pass)
UserRoles (ID, Description)
UserAccountRoles (UserAccountID, UserRoleID)
I use these to define the roles of each user (and therefore the permissions they have) within my application. I want to construct a query to return each user in one column, followed by columns for each role containing a boolean value indicating whether or not they are in this role (i.e. whether a record exists in the UserAccountRoles table).
I can do something like this to get a list but it's not the most user friendly way of presenting it to the user:
SELECT UserAccounts.ID, UserAccounts.UserNick, UserRoles.Description FROM ((UserAccounts LEFT JOIN UserAccountRoles ON UserAccounts.ID = UserAccountRoles.UserAccountID) LEFT JOIN UserRoles ON UserAccountRoles.UserRoleID = UserRoles.ID) ORDER BY UserAccounts.UserNick
I generally get on pretty well with SQL but I can't even figure out where to get started with this one! Is it even possible? Any help greatly appreciated!
Mar 1, 2011, 08:02 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 59 Post(s)
- 3 Thread(s)
Mar 1, 2011, 08:08 #3
Wow - 3 minutes... I see why your avatar is a clock now!
Thanks for your help. Very much appreciated.
Mar 1, 2011, 15:51 #4
- Join Date
- Apr 2004
- Miami, FL, USA
- 0 Post(s)
- 0 Thread(s)
You can accomplish this with a denormalization query... I've written a detailed blog on how to do this here:
The basic requirement is that you need a finite number of properties to display as columns.
Here's an example of how your query might look using your schema:
Obviously, you'll have to change "ROLEx" for the names of your roles.
SELECT ua.ID, MAX(CASE WHEN st.name = 'ROLE1' THEN 1 ELSE 0 END) AS ROLE1, MAX(CASE WHEN st.name = 'ROLE2' THEN 1 ELSE 0 END) AS ROLE2, MAX(CASE WHEN st.name = 'ROLE3' THEN 1 ELSE 0 END) AS ROLE3, MAX(CASE WHEN st.name = 'ROLE4' THEN 1 ELSE 0 END) AS ROLE4 FROM UserAccounts AS ua INNER JOIN UserAccountRoles AS uar ON ua.ID = uar.UserAccountID INNER JOIN UserRoles AS AS ur ON ur.ID = uar.UserRoleID GROUP BY ua.ID