MySQL JOIN from 3 relative tables

I have 3 tables

1. role (id, ...)

2. permission (id, ...)

3. role_permission (id, role_id, permission_id)

I want to do the next: i should select all roles and all permissions for this roles with help of one query. My test query:

SELECT `rp`.*, `r`.`role`, `r`.`id` AS `rol开发者_如何学编程e_id`, `p`.`id` AS 
`permission_id`,`p`.`name` AS `permission` FROM `role_permission` AS `rp` RIGHT JOIN 
`role` AS `r` ON r.id = rp.role_id RIGHT JOIN `permission` AS `p` ON p.id = rp.permission_id

But this query select only those roles, what have permissions. But i need to select all roles and all permissions (exist row in role_permission or not).

Thank you in advance. Sorry for my english.

You want a left join.

select
    r.role, 
    r.id as role_id,
    p.id as permission_id

from role

left join role_permission rp on rp.role_id = role.id
left join permission p on p.id = rp.permission_id

This will give you the permissions for all roles with permissions, as well as a null permission_id for roles without permissions.

You need to select all roles in a query, then all permissions in another, cross them and finally left join to role_permissions to produce the full list of roles x permissions

SELECT rp.*, r.role, r.id AS role_id, p.id AS permission_id,p.name AS permission
FROM permission AS p
CROSS JOIN  role AS r
LEFT JOIN role_permission AS rp ON p.id = rp.permission_id
  AND r.id = rp.role_id

The cross join gives you a full matrix of role x permission combinations, and the LEFT join succeeds even if the row in role_permission doesn't have any entries for a row (role, permission) in the full matrix.