SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot infoxicated's Avatar
    Join Date
    Jun 2001
    Location
    UK
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with a 3 table + left join query

    Hi there,

    I have three tables - the user table, a site_areas table, and a user_permissions table.

    the first two speaks for itself, the third one contains user_id, area_id, access, which is set to true or false.

    What I'm trying to do is return a full set of results from the site_areas table for every user, along with their permissions for that area should they exist.

    This is what I have so far:
    Code:
      SELECT 
        site_areas.id as area_id, 
        site_areas.title, 
        user_permissions.id as permission_id, 
        user_permissions.user_id, 
        user_table.username, 
        user_permissions.access 
     FROM site_areas LEFT JOIN user_permissions ON site_areas.id = user_permissions.area_id , user_table 
     WHERE user_permissions.user_id = user_table.user_id 
     ORDER BY username,title
    Sadly that's not giving me a full set of results from the site_areas table for each user - just the site_area rows where the user has a permission value set in the user_permissions table.

    Any help would be well received.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    SELECT 
        site_areas.id as area_id, 
        site_areas.title, 
        user_permissions.id as permission_id, 
        user_permissions.user_id, 
        user_table.username, 
        user_permissions.access 
     FROM site_areas LEFT JOIN user_permissions ON site_areas.id = user_permissions.area_id left join user_table 
     on user_permissions.user_id = user_table.user_id 
     ORDER BY username,title

  3. #3
    SitePoint Zealot infoxicated's Avatar
    Join Date
    Jun 2001
    Location
    UK
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the suggestion, but that returns the same result as my own query above.

    Anybody else have any ideas?

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Could you give some sample data, your expected result and compare that to the result you are currently getting.

  5. #5
    SitePoint Zealot infoxicated's Avatar
    Join Date
    Jun 2001
    Location
    UK
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I took a step back in the end and just decided to do it on a user by user basis rather than having it return a user for each user_id in the user_permissions table. So it became a two table query rather than a three table monstrosity!
    Code:
     		SELECT 
     		 sa.id as area_id, 
     		 sa.title, 
     		 up.id as permission_id, 
     		 up.user_id, 
     		 up.access 
     		FROM site_areas sa
        	LEFT JOIN user_permissions up ON (sa.id = up.area_id and up.user_id = 2)	   
     		ORDER BY title
    So now I'll just have a form with select box with an option for each user, and when you submit it'll do the query above with the user_id.

    Not the way I had it in my head originally, but at least this way is more transparent and less intensive on the MySQL side of things.

    Thanks for your help anyway.

    This actually continues my streak of never getting an answer via sitepoint. I usually end up figuring it out myself before anyone can help me... just unlucky, I guess.


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
  •