MySQL group_concat with where clause

Question

I got this problem with Group_Concat and a where filter. In my table i got module names which are linked to a client. I want to search clients by module name, but in the group concat i still want to see all modules that are owned by the client. currently it will display all clients with those modules, but it will only display that specific module. I can't figure out how to make them both work together.

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

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          | 2          |
| 1          | 3          |
| 2          | 1          |
| 2          | 2          |
| 2          | 4          |
| 3          | 4          |
| 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.id IN (1,2)

Results:

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

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

you use `WHERE client_module.module_id IN (1,2)` in your query ,what is wrong here? — Hamidreza, Mar 10 '14 at 10:41
my bad, that wasn't in the original query stripped the wrong part of. Corrected it — telefoontoestel, Mar 10 '14 at 10:47
You are using `client_module.module_id IN (1,2)` so it'll obviously pick only module1 and module2..Remove the where clause to get the expected output — G one, Mar 10 '14 at 10:50
@Gone - No, if where clause is removed, *all* clients will be listed. The goal is to filter by clients that have entries for module1 or module2, but then list all modules for those clients. — ToolmakerSteve, Jul 08 '20 at 01:38

score 18 · Accepted Answer · answered Mar 10 '14 at 10:53

You can Try Like this.

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 Find_In_Set('module1',modules)>0 or Find_In_Set('module2',modules)>0

SQL Fiddle Demo

score 2 · Answer 2 · answered Mar 10 '14 at 10:53

2

You are using client_module.module_id change it to client_module.client_id. Use group by with group_cancat

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      client_module.client_id IN (1,2,4)
group by client.id, client.name

fiddle

answered Mar 10 '14 at 10:53

G one

2,679
2
14
18

This is a useful query, however it does not do what OP is asking. The question is to filter clients by specifying *modules*, then list all modules for those clients. This query requires supplying a list of *client* ids. – ToolmakerSteve Jul 08 '20 at 01:41

score 1 · Answer 3 · answered Mar 10 '14 at 10:51

1

try that

   SELECT     client.id, client.name, GROUP_CONCAT(module.id) 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      client.id IN (1,2,4) 
   group by client.id

You have this wrong client_module.module.id fixed to client_module.module_id
AND you are already saying to your WHERE clause to return only 1 and 2 .
Added Group by to work when you have Group_Concat

DEMO HERE

answered Mar 10 '14 at 10:51

echo_Me

37,078
5
58
78

This is a useful query, however it doesn't do what OP asked. OP wants to *only* list clients *that do have rows for module1 or module2*; but he then wants to see a list of *all* modules for each of those clients. What your answer does instead is list all modules for clients *selected by client id*. – ToolmakerSteve Jul 08 '20 at 01:36

MySQL group_concat with where clause

3 Answers3

Linked