We can concatenate multiple MySQL rows into one field using GROUP_CONCAT function in mySQL query. There might be situations when you select multiple values from multiple tables and after all the joins you have got a lot of rows than you would like. We can useGROUP_CONCAT to obtain the same result with less numbers of rows.
The full syntax from MySQL documentation:
0 1 2 3 4 | GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val]) |
- The
DISTINCTclause is used to eliminate duplicate values in each row returned. - To sort values in the result, use the
ORDER BYclause. - Add the
DESCkeyword to the name of the column you are sorting in descending order by in theORDER BYclause. - The default separator between values in a group is comma (“,”).
- To specify a separator explicitly, use
SEPARATORfollowed by the string literal value that should be inserted between group values. - To eliminate the separator altogether, specify
SEPARATOR ''.
Let’s make it more clear with a real world example:
So for example you have the following 3 table representing shows in a theater, players and participation of players among those shows:
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE TABLE `shows` ( `sid` int(2) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`sid`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `players` ( `pid` int(2) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`pid`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `show_player` ( `id` int(2) NOT NULL AUTO_INCREMENT, `sid` int(2) NOT NULL, `pid` int(2) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
Now we wish to concatenate names of players in single field for each show using GROUP_CONCAT. So we created the query as per following:
|  |
If you duplicate any (sid, pid) set from show_player table above and skip DISTINCT keyword from query then you will see duplicate entry of pid in pid_list column.
Now to get result in more readable format we can modify query and use GROUP_CONCAT and GROUP the results by sid:
0 1 2 3 4 5 | SELECT s.name AS `show`, GROUP_CONCAT(p.name ORDER BY p.name SEPARATOR ', ' ) AS team FROM show_player sp, shows s, players p WHERE sp.sid = s.sid AND sp.pid = p.pid GROUP BY sp.sid ORDER BY s.name |
In the query we have covered show with backtick in the query because this is a MySQL keyword. Read syntax error due to using a reserved word as a table or column name to know more in detail.
Note: There is a 1024 byte limit on result. It means the result is truncated to the maximum length that is given by the
group_concat_max_lensystem variable, which has a default value of 1024. To solve this, run this query before your query:
0 1 | SET group_concat_max_len = 2048 |
You can also change 2048 according to your needs.
I hope this little demo to concatenate multiple MySQL rows into one field using GROUP_CONCAT was helpful to you. If you have any questions or comments, feel free to use the comment form below.
how do I get players who haven’t been to any show?
You can either use LEFT JOIN or NOT IN clause. Thought JOIN is more optimized.
SELECT * FROM players WHERE pid NOT IN (SELECT pid FROM show_player)Thank you Amit, very helpful.
How can I select multiple values from a column from the left joined table using WHERE with AND for the same column name!?
Hi,
Use something like this:
SELECT t1.col FROM table_name t1 LEFT JOIN table_name t2 ON t1.col = t2.col WHERE t1.some_col = ‘some_value’ AND t1.other_col = ‘other_value’