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:

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

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.

select parent_id, group_concat(child_id) 
from parentChild
group by parent_id;
+-----------+----------+
| parent_id | children |
+-----------+----------+
|         1 | 1,2,3    |
|         2 | 1,2,3    |
|         3 | 1,2,3    |
|         4 | 1,2,3    |
|         5 | 1,2,3    |
+-----------+----------+

Search within Codexpedia

Custom Search

Search the entire web

Custom Search