MySQL JOIN / UNION DISTINCT issue

Question

I am working on a project where users need to have permissions based on which department they are connected to.

So now I am trying to do a query that will show me users that have all necessary permissions based on the users department_id (users that do not have all necessary permissions shall not be displayed in the result).

I have been trying lot of different things (different type of JOINS, sub queries w/where in, exists and union distinct) without luck, this kind of query is next level for me and challenges my logical thinking.

Tables:

users
    id
    first_name
    last_name
    department_id

permissions
    id
    name

departments (relation to groups and categories based on department)
    id 
    name
    category_id
    group_id

categories
    id
    name

groups
    id
    name

departments_permissions (permission requirement)
    department_id
    permission_id

categories_permissions (permission requirement)
    category_id
    permission_id

groups_permissions (permission requirement)
    group_id
    permission_id

users_permissions (users have permissions here)
    user_id
    permission_id

Department

• A department CAN (not required) have many permissions (departments_permissions).
• A department CAN (not required) have a category.
• A department CAN (not required) have a group.

Category

• A category CAN (not required) have many permissions (categories_permissions).

Group

• A group CAN (not required) have many permissions (groups_permissions).

Important side note

departments_permissions, categories_permissions and groups_permissions can contain one or more of the same permission_id's, therefore we have to select distinct values.

Example data

https://pastebin.com/BvhAznpY

My latest query attempt:

SELECT 
    `users`.id, 
    `users`.first_name, 
    `users`.last_name,
    `users`.department_id
FROM 
    `users`
INNER JOIN 
    `departments` ON `users`.department_id = `departments`.id 
INNER JOIN 
    `users_permissions` ON `users`.id = `users_permissions`.user_id 
INNER JOIN 
    (
        SELECT permission_id FROM departments_permissions WHERE department_id = departments.id
        UNION DISTINCT
        SELECT permission_id FROM categories_permissions WHERE category_id = departments.group_id
        UNION DISTINCT
        SELECT permission_id FROM groups_permissions WHERE group_id = departments.group_id
    ) AS tblPermissionsRequired ON `users_permissions`.permission_id = `tblPermissionsRequired`.permission_id
GROUP BY 
    `users_permissions`.user_id;

Answer 1

I think what you need is something like this:

SELECT u.first_name,u.last_name,p.name
FROM users u, user_permissions up, permissions p
WHERE u.id=up.user_id AND
up.permission_id=p.id
UNION
SELECT u.first_name,u.last_name,p.name
FROM users u, departments d, departments_permission dp, permissions p
WHERE u.department_id=d.id
AND d.id=dp.department_id
AND dp.permission_id=p.id
UNION
SELECT u.first_name,u.last_name,p.name
FROM users u, departments d, categories_permission cp, permissions p
WHERE u.department_id=d.id
AND d.category_id=cp.category_id
AND cp.permission_id=p.id
UNION
SELECT u.first_name,u.last_name,p.name
FROM users u, departments d, groups_permission gp, permissions p
WHERE u.department_id=d.id
AND d.group_id=gp.group_id
AND gp.permission_id=p.id

Here's a hint:

• Create a VIEW with the query above
• Do a SELECT DISTINCT * on the view

Answer 2

Build 2 tables (sub-queries) to list

• all the permission a user required (i.e. user_permissions)
• all the permission a user have (i.e. 3 unions similar to what you have done)

Join them and group by id would find the the number of required and how many of them are given already.

Finally, add a filter clause (using HAVING). Then you got the list of user ID :)

select
  up.user_id,
  max(u.first_name) as first_name,
  max(u.last_name) as last_name,
  count(up.permission_id) as permission_required,
  count(perm.permission_id) as permission_have
from
  users u
join
  users_permissions up on u.id = up.user_id
left join
(
  SELECT users.id as user_id, permission_id
  FROM users
  INNER JOIN departments ON users.department_id = departments.id
  INNER JOIN departments_permissions on departments_permissions.department_id = departments.id

  UNION

  SELECT users.id, permission_id
  FROM users
  INNER JOIN departments ON users.department_id = departments.id
  INNER JOIN categories_permissions on categories_permissions.category_id = departments.category_id

  UNION

  SELECT users.id, permission_id
  FROM users
  INNER JOIN departments ON users.department_id = departments.id
  INNER JOIN groups_permissions  on groups_permissions.group_id = groups_permissions.group_id
) perm
on up.user_id = perm.user_id and up.permission_id = perm.permission_id
group by up.user_id
having count(up.permission_id)  = count(perm.permission_id)

Answer 3

This following script should work-

SELECT A.id,A.first_name,A.last_name,A.department_id 
FROM
(
    SELECT U.*,
    A.Dept_id AS Dept_ID,
    A.permission_id AS [Dept_Wise_Permissiion_List],
    UP.permission_id
    FROM users U
    INNER JOIN (
            SELECT D.id Dept_id,CP.permission_id  
            FROM departments D INNER JOIN categories_permissions CP ON D.category_id = CP.category_id 

            UNION ALL

            SELECT D.id Dept_id,GP.permission_id  
            FROM departments D INNER JOIN groups_permissions GP ON D.group_id = GP.group_id

            UNION ALL

            SELECT D.id Dept_id, DP.permission_id
            FROM departments D INNER JOIN departments_permissions DP ON D.id = DP.department_id

    ) A ON U.department_id =A. Dept_id
    LEFT JOIN users_permissions UP 
        ON U.id = UP.user_id AND A.permission_id = UP.permission_id
)A
GROUP BY A.id,A.first_name,A.last_name,A.department_id 
HAVING COUNT(ISNULL(A.permission_id,1)) = COUNT(A.permission_id)