SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot davejuk's Avatar
    Join Date
    Apr 2006
    Location
    UK
    Posts
    188
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Returning values as field names

    Hi

    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:

    Code SQL:
    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!

    Regards,

    Dave

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    it is possible but only if you hard-code all the roles

    this type of layout is called a crosstab, and it is much better to do it in the application layer than in sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot davejuk's Avatar
    Join Date
    Apr 2006
    Location
    UK
    Posts
    188
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow - 3 minutes... I see why your avatar is a clock now!

    Thanks for your help. Very much appreciated.

  4. #4
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can accomplish this with a denormalization query... I've written a detailed blog on how to do this here:

    http://www.transio.com/content/how-d...able-using-sql

    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.

    Cheers!

    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •