Is there a fast way in SQL/PHP to get all users with all branches permission?

0
votes

I have a database table of branch_user_permission that has 3 table columns of [branch_id, permission_id, user_id]

Here is the sample data of the database

Sample Data of Branch Permission User

I just want to map or get all users where all permissions (permission_id) are the same with other branches (branch_id)

Example: 

[branch_id][permission_id][user_id]


[1][2][1]

[1][3][1]

[1][5][1]

[1][6][1]

[2][2][2]

[2][3][2]

[2][5][2]

[2][6][2]

[3][2][3]

[3][3][3]

[3][6][3]

[4][2][5]

[4][3][5]

[4][5][5]

[4][6][5]

So the expected output that I will get for getting all user_ids where all permission are the same from all other branches:

  • Array (1,2,5)
1
phpsqloptimization
How would you know Array (1,2,5) is correct and not Array (3)? I think you need to establish the set of expected permissions somehow or correct answer would be just groups of ids with same permissions [[1,2,5], [3]] - shudder
3 is not included because the permission_id for user_id of 3 is [2,3,6] only. Unlike for user_id for (1,2,5), the permission_id is [2,3,5,6] - Star
I know why it's not included WITH 1,2,5 but I still don't know why it's not the correct result. Is it because there is no other user with such permissions? What would be correct result if other user has 2,3,6 permissions or the same number of users in more than one group have same sets of permissions that are different from other groups of users? - shudder
The main objective to get the users is I want to get all the users with the same permissions with other branches. For example I have permission 2,3,6 I want to check if on other branches has also permission 2,3,6. - Star
You say "other branches" so you have predetrmined branch id (thus its set of permissions) right? - shudder

1 Answers

0
votes

This would work for known set of permissions. You need to match all of them so that last value 4 is the number of permissions ids in the set (2,3,5,6)

SELECT user_id FROM branch_user_permission
WHERE permission_id IN (2,3,5,6)
GROUP BY user_id
HAVING COUNT(branch_id) = 4

I don't know how you determine set of permissions to look for, but if it comes from inital (known) branch_id or user_id you might need another query to build it (and count). Doing that in single query you would need both count and list of ids, which may require two subqueries - don't have time to check now.

SqlFiddle DEMO