Help with a 3 table + left join query

[infoxicated]

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:

  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.

[swampBoogie]

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

[infoxicated]

Thanks for the suggestion, but that returns the same result as my own query above.

Anybody else have any ideas?

[swampBoogie]

Could you give some sample data, your expected result and compare that to the result you are currently getting.

[infoxicated]

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!

 		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.