How to correctly compose sql query for my task?

Question

PHP

There are three tables `user`, `user_role`, `access_group`

In `user` user info (login, password, etc.)

In `access_group` roles or role groups (one user can have many roles)


In `user_role ` relationship between tables above.


The task is to display information about the user and his role in the format:
| log user | | email_user | role1, role2, role3 |

But the catch is still in the search for roles. You can specify multiple roles to search. Those. search for users for example with role1, role2 and display information about them with all roles including role3.

I wrote a cumbersome query to solve this problem. Maybe you can give me a better solution.

SELECT
    `t1`.*,
    GROUP_CONCAT(`t3`.`name` SEPARATOR ', ') AS `role_group`
FROM
    `user` AS `t1`
JOIN `user_role` AS `t2`
ON
    `t1`.`id` = `t2`.`user_id`
JOIN `access_group` AS `t3`
ON
    `t2`.`group_id` = `t3`.`id`
WHERE
    `t1`.`id` IN(
    SELECT
        `user_id`
    FROM
        `user_role`
    WHERE
        `group_id` IN('20', '24')
    GROUP BY
        `user_id`
    HAVING
        COUNT(`user_id`) >= 2
)
GROUP BY
    `t1`.`id`
LIMIT 0, 20

To be honest, he scares me

Answer 1

You can simplify the query like this:

SELECT
    `t1`.*,
    GROUP_CONCAT(`t3`.`name` SEPARATOR ', ') AS `role_group`
FROM
    `user` AS `t1`
JOIN `user_role` AS `t2`
ON
    `t1`.`id` = `t2`.`user_id`
JOIN `access_group` AS `t3`
ON
    `t2`.`group_id` = `t3`.`id`
GROUP BY
    `t1`.`id`
HAVING SUM(`group_id` IN('20', '24'))  = 2
LIMIT 0, 20

MySQL fiddle