SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE `user` ( id int auto_increment primary key, name varchar(64) ); insert into `user` values (1, 'User1'), (2, 'User2'); CREATE TABLE `access_group` ( id int auto_increment primary key, name varchar(64) ); insert into `access_group` values (20, 'Role20'), (24, 'Role24'), (30, 'Role30'); CREATE TABLE `user_role` ( user_id int, group_id int, PRIMARY KEY (user_id, group_id) ); INSERT INTO `user_role` VALUES (1, 20), (1, 24), (1, 30), (2, 24), (2, 30); 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; 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