Home > other >  Check all possible connection exists
Check all possible connection exists

Time:08-24

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

  • Related