Mysql concatenating child values into one

group_concat is a mysql function to group multiple rows of one to many relationship records into one row. For example, a parent can have more than one child. A representation of this relation in a table could be:
[code language=”sql”]
create table parentChild(
parent_id int(1) default null,
child_id int(1) default null,
primary key (parent_id, child_id)
);
insert into parentChild(parent_id, child_id)
values
(1,1),(1,2),(1,3),
(2,1),(2,2),(2,3),
(3,1),(3,2),(3,3),
(4,1),(4,2),(4,3),
(5,1),(5,2),(5,3);
select * from parentChild;
[/code]
[code language=”text”]
+———–+———-+
| parent_id | child_id |
+———–+———-+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |
| 5 | 1 |
| 5 | 2 |
| 5 | 3 |
+———–+———-+
[/code]

When we do a simple select from this table above, the result is each parent has 3 childrens thus 3 rows for each parent. It is not visually satisfying because parent_id is repeating itself. It will be more readable if the parent_id is not repeating for each different child_id. The mysql function group_concat comes in handy to beautify the result from the select. It will concatenate the child_ids into one comma separated string.
[code language=”sql”]
select parent_id, group_concat(child_id)
from parentChild
group by parent_id;
[/code]
[code language=”text”]
+———–+———-+
| parent_id | children |
+———–+———-+
| 1 | 1,2,3 |
| 2 | 1,2,3 |
| 3 | 1,2,3 |
| 4 | 1,2,3 |
| 5 | 1,2,3 |
+———–+———-+
[/code]