Joining 3 tables - doing joins how to

This is my current query - its not getting the required result. I want it do display all of the "resources" even if they dont have a connection.

SELECT *
FROM (`user_permissions`)
JOIN `user_groups` ON `user_groups`.`id` = `user_permissions`.`role`
JOIN `user_resources` ON `user_resources`.`id` = `user_permissions`.`resource`
WHERE `role` = '4'

When I try left join or right join it still returns the same result. The result I get is:

id   | role  | resource | name

5    | 4     |        2 | Changelog 

I want

id   | role  | resource | name

5    | 4    |   2 | Changelog 
null | null | null | Resource2 
null | null | null | Resource3

Is this possib开发者_开发知识库le?

Looking through your query, role is part of user_permissions which is one of the connections that may or may not exist. Consider changing your where clause to WHERE `role`= '4' OR `role` IS NULL if you want to display these null records as well...

Also, although this can be accomplished by a right join, I believe it's more readable/understandable if you select from user_resources instead, then left join on the other tables. This follows from your problem description statement; you want to "display all of the 'resources' even if they don't have a connection", which means you want to select from resources, then join on any connections if they exist.

in your sql you ask for

WHERE `role` = '4'

and want an result that has null in role ? null is not 4, so there is no such result.

First: your where clause will always restrict the result set. You have a condition that requires role to be "4" -- so it doesn't display any results unless the result has "4" in the role column.

Second: where is "name" coming from -- since that is the one you want to appear, you need to have the table with that column as your base table and LEFT JOIN the other two tables.

Assuming "name" is coming from resources, your query should read:

SELECT *
FROM `user_resources`
LEFT JOIN `user_permissions` ON `user_permissions`.`resource` = `user_resources`.`id`
LEFT JOIN `user_groups` ON `user_groups`.`id` = `user_permissions`.`role`

SELECT * FROM (user_permissions) JOIN user_groups ON user_groups.id = user_permissions.role JOIN user_resources ON user_resources.id = user_permissions.resource WHERE (role = '4' or role is null)

you will get result from this query