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 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