MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table client  (
id int,
name varchar(20));

insert into client  values 
(1,'client1'),
(2,'client2'),
(3,'client3'),
(4,'client4');

create table module  (
id int,
name varchar(20));

insert into module  values 


(1,'module1'),
(2,'module2'),
(3,'module3'),
(4,'module4');

create table client_module (
client_id int,
module_id int );

insert into client_module values 
(1,1),
(1,3),
(2,1),
(2,2),
(2,4),
(3,2),
(4,1),
(4,2),
(4,3),
(4,4);







SELECT  c.id, c.name, GROUP_CONCAT(m.name) AS modules
FROM    client AS c
        INNER JOIN client_module AS cm
            ON cm.client_id = c.id
        INNER JOIN module AS m
            ON m.id = cm.module_id
WHERE   EXISTS
        (   SELECT  1
            FROM    client_module AS cm2
                    INNER JOIN module AS m2
                        ON m2.id = cm2.module_id
            WHERE   m2.name LIKE '%module1%' 
            AND     cm2.client_id = c.id
        )
GROUP BY  c.id, c.name;


id
name
modules


1
client1
module1,module3

2
client2
module1,module2,module4

4
client4
module1,module2,module3,module4


SELECT  c.id, c.name, GROUP_CONCAT(m.name) AS modules
FROM    client AS c
        INNER JOIN client_module AS cm
            ON cm.client_id = c.id
        INNER JOIN module AS m
            ON m.id = cm.module_id
GROUP BY  c.id, c.name
HAVING COUNT(CASE WHEN m.name LIKE '%module1%' THEN 1 END) > 0;


id
name
modules


1
client1
module1,module3

2
client2
module1,module2,module4

4
client4
module1,module2,module3,module4


EXPLAIN
SELECT  c.id, c.name, GROUP_CONCAT(m.name) AS modules
FROM    client AS c
        INNER JOIN client_module AS cm
            ON cm.client_id = c.id
        INNER JOIN module AS m
            ON m.id = cm.module_id
WHERE   EXISTS
        (   SELECT  1
            FROM    client_module AS cm2
                    INNER JOIN module AS m2
                        ON m2.id = cm2.module_id
            WHERE   m2.name LIKE '%module1%' 
            AND     cm2.client_id = c.id
        )
GROUP BY  c.id, c.name;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
m2
null
ALL
null
null
null
null
4
25.00
Using where; Using temporary; Using filesort; Start temporary

1
SIMPLE
cm2
null
ALL
null
null
null
null
10
10.00
Using where; Using join buffer (hash join)

1
SIMPLE
c
null
ALL
null
null
null
null
4
25.00
Using where; End temporary; Using join buffer (hash join)

1
SIMPLE
cm
null
ALL
null
null
null
null
10
10.00
Using where; Using join buffer (hash join)

1
SIMPLE
m
null
ALL
null
null
null
null
4
25.00
Using where; Using join buffer (hash join)


EXPLAIN
SELECT  c.id, c.name, GROUP_CONCAT(m.name) AS modules
FROM    client AS c
        INNER JOIN client_module AS cm
            ON cm.client_id = c.id
        INNER JOIN module AS m
            ON m.id = cm.module_id
GROUP BY  c.id, c.name
HAVING COUNT(CASE WHEN m.name LIKE '%module1%' THEN 1 END) > 0;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
c
null
ALL
null
null
null
null
4
100.00
Using temporary; Using filesort

1
SIMPLE
cm
null
ALL
null
null
null
null
10
10.00
Using where; Using join buffer (hash join)

1
SIMPLE
m
null
ALL
null
null
null
null
4
25.00
Using where; Using join buffer (hash join)

id	name	modules
1	client1	module1,module3
2	client2	module1,module2,module4
4	client4	module1,module2,module3,module4

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	m2	null	ALL	null	null	null	null	4	25.00	Using where; Using temporary; Using filesort; Start temporary
1	SIMPLE	cm2	null	ALL	null	null	null	null	10	10.00	Using where; Using join buffer (hash join)
1	SIMPLE	c	null	ALL	null	null	null	null	4	25.00	Using where; End temporary; Using join buffer (hash join)
1	SIMPLE	cm	null	ALL	null	null	null	null	10	10.00	Using where; Using join buffer (hash join)
1	SIMPLE	m	null	ALL	null	null	null	null	4	25.00	Using where; Using join buffer (hash join)