MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.27


create table permissions (
id int,
controller varchar(20),
action varchar(10)
);

create table roles_permissions (
role_id int,
permission_id int
);

create table roles (
role_id int,
role_name varchar(10)
);

insert into permissions values
(5,	'Error',	'index'),
(2,	'Home',	'index'),
(3,	'Home',	'login'),
(4,	'Home',	'logout'),
(1,	'Test',	'index'),
(9,	'UserManagement',	'createUser'),
(8,	'UserManagement',	'editUser'),
(6,	'UserManagement',	'index');

insert into roles_permissions values
(1,	2),
(1,	3),
(1,	5),
(2,	1),
(2,	2),
(2,	3),
(2,	4),
(2,	5),
(3,	1),
(3,	2),
(3,	3),
(3,	4),
(3,	5),
(4,	1),
(4,	2),
(4,	3),
(4,	4),
(4,	5),
(4,	6),
(4,	8),
(4,	9);

insert into roles values
(4,	'Admin'),
(2,	'special'),
(3,	'User'),
(1,	'Visitor');
SET @role_name = 'special';

SELECT 
    p.*,
    IF(ISNULL(rp.role_id), 'no', 'yes') AS exist
FROM permissions p
LEFT JOIN roles_permissions rp ON rp.permission_id = p.id AND rp.role_id = (
    SELECT role_id FROM roles WHERE role_name = @role_name
)


id
controller
action
exist


5
Error
index
yes

2
Home
index
yes

3
Home
login
yes

4
Home
logout
yes

1
Test
index
yes

9
UserManagement
createUser
no

8
UserManagement
editUser
no

6
UserManagement
index
no

id	controller	action	exist
5	Error	index	yes
2	Home	index	yes
3	Home	login	yes
4	Home	logout	yes
1	Test	index	yes
9	UserManagement	createUser	no
8	UserManagement	editUser	no
6	UserManagement	index	no