Check all possible connection exists-CodePudding

I want to check for all connections between two table exists and return all rows specifying weather the connections exists or not for example here is my tables:

Permissions:

index	Controller	Action
5	Error	index
2	Home	index
3	Home	login
4	Home	logout
1	Test	index
9	UserManagement	createUser
8	UserManagement	editUser
6	UserManagement	index

Roles_Permissions:

Roles	Permissions
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

Roles:

Role_id	Role_name
4	Admin
2	special
3	User
1	Visitor

if the query variable is Special I want following rows returned:

index	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

Foreign key exists between role_id and perm_id

CodePudding user response：

I suppose you need something like this

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
)

Output

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

You can check a working demo here