Table1
| t_id | code | name |
|---|---|---|
| 1 | PM123 | Hello |
| 2 | PM456 | Hello2 |
Table2
| id | t_id | value |
|---|---|---|
| 1 | 1 | something1 |
| 2 | 1 | something2 |
| 3 | 1 | something3 |
| 4 | 2 | baby1 |
| 5 | 2 | baby2 |
I want to get a result as following:
| t_id | code | name | value | value | value |
|---|---|---|---|---|---|
| 1 | PM123 | Hello | something1 | something2 | something3 |
| 2 | PM456 | Hello2 | baby1 | baby2 |
In this case, if we use LEFT JOIN the result will fetch to 5 columns data, if we use GROUP BY, it can come back with 2 columns of data but the row "value" can only come up with either 1 value of the grouped row due the order priority. So, how can we make the expected result?
Here introduce a function GROUP_CONCAT, GROUP_CONCAT can combine the datas of a row and return a dataset with comma separated format.
For the above example, we can write the sql statement as follow:
SELECT t_id, code, name, GROUP_CONCAT(value) as value FROM table1 LEFT JOIN table 2 ON table1.t_id=table2.t_id GROUP BY table2.t_id
And the result would be as follow:
| t_id | code | name | value |
|---|---|---|---|
| 1 | PM123 | Hello | something1,something2,something3 |
| 2 | PM456 | Hello2 | baby1,baby2 |
In addition, when using GROUP_CONCAT , we can also add some self defined symbols in a combined dataset. For example, if we change GROUP_CONCAT(value) as value to GROUP_CONCAT(id,'-',value) as value in the above example, the result would become 1-something1,2-something2,3-something3, this help up solve many case of requirement..