LIKE group_concat with where clause-CodePudding

i am using Like in group concat & i want to get all modules attached to client even if it does not match to LIKE, i know that that i can do that with HAVING but i don't want to use HAVING, any solution with WHERE or in join condition ?

Any suggestions on how to get my expected result??

These are some basic tables and the query i tried along with results i get and the result i really wanted

Tables

Client
 -------------------- 
| id      |  name    |
 -------------------- 
| 1       | client1  |
| 2       | client2  |
| 3       | client3  |
| 4       | client4  |
 -------------------- 

Module
 -------------------- 
| id      |  name    |
 -------------------- 
| 1       | module1  |
| 2       | module2  |
| 3       | module3  |
| 4       | module4  |
 -------------------- 

Client_Module
 ------------------------- 
| client_id  | module_id  |
 ------------------------- 
| 1          | 1          |
| 1          | 3          |
| 2          | 1          |
| 2          | 2          |
| 2          | 4          |
| 3          | 2          |
| 4          | 1          |
| 4          | 2          |
| 4          | 3          |
| 4          | 4          |
 -------------------------

Query:

SELECT     client.id, client.name, GROUP_CONCAT(module.name) AS modules
FROM       client
LEFT JOIN  client_module ON client_module.client_id = client.id
LEFT JOIN  module ON module.id = client_module.module.id
WHERE      module.name LIKE '%module1%'
group by client.id

Results:

Received
 -------------------------------------------------- 
| id     | name     | modules                      |
 -------------------------------------------------- 
| 1      | client1  | module1                      |
| 2      | client2  | module1                      |
| 4      | client4  | module1                      |
 -------------------------------------------------- 

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

CodePudding user response：

This is possible without HAVING by using EXISTS:

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;

But this is less efficient, and more verbose so offers no advantage whatsoever. I expect your reasons for using HAVING are largely unfounded, and the approach I would personally take is with a conditional count in the HAVING clause:

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;

Example on db<>fiddle

CodePudding user response：

This should work

SELECT     client.id, client.name, GROUP_CONCAT(module.name) AS modules
FROM       client
LEFT JOIN  client_module ON client_module.client_id = client.id
LEFT JOIN  module ON module.id = client_module.module.id
GROUP BY   client.id
HAVING     modules LIKE '%module1%'