User97798 User97798 - 4 months ago 7
MySQL Question

It is possible to get make array in cell from another tables result in mysql

I Have two table and i have get result from both of table
Here is the table

Table1
+-------------------+
| APH | ID |
+-------------------+
| A | ID01 |
| B | ID02 |
| C | ID03 |
| D | ID04 |
+-------------------+

Table2
+-------------------+
| ID | Value |
+-------------------+
| ID01 | 10 |
| ID01 | 15 |
| ID01 | 20 |
| ID01 | 25 |
| ID02 | 6 |
| ID02 | 18 |
| ID02 | 30 |
| ID02 | 42 |
| ID02 | 54 |
| ID03 | 7 |
| ID03 | 15 |
| ID03 | 23 |
| ID03 | 31 |
| ID04 | 4 |
| ID04 | 12 |
| ID04 | 20 |
+-------------------+


For Example:-

I have to get all value from table where ID = ID01

so is it possible to get result like this.?

Result
+-----------------------------------+
| APH | ID | Value |
+-----------------------------------+
| A | ID01 |[10,15,20,25] |
+-----------------------------------+

Answer

You need to use GROUP_CONCAT

MySQL GROUP_CONCAT() function returns a string with concatenated non-NULL value from a group.

Returns NULL when there are no non-NULL values.

SELECT 
 APH,
 ID,
 CONCAT('[',GROUP_CONCAT(T2.`value`),']') AS output
FROM Table1 AS T1 
INNER JOIN Table2 AS T2
ON T1.ID = T2.ID
WHERE T1.ID ='ID01'

EDIT:

Just leave the CONCAT part if you only want comma separated list.

SELECT 
 APH,
 ID,
 GROUP_CONCAT(T2.`value`) AS output
FROM Table1 AS T1 
INNER JOIN Table2 AS T2
ON T1.ID = T2.ID
WHERE T1.ID ='ID01'