Adam Adam - 5 months ago 18
MySQL Question

MySQL group_concat and concat surname, lastname and title

I have two tables

places


id | name | year |
--------------------
1 | Berlin | 1966 |
2 | Paris | 2012 |



and

member


id | title | surname | lastname | idPlace |
-------------------------------------------
1 | Dr | Maxi | Bax | 1 |
2 | | Ameli | Steifer | 2 |
3 | | Wonder | Woman | 1 |
4 | | Harry | Barry | 2 |



As a result I would like to get


name | year | members |
----------------------------------------------
Berlin | 2011 | Dr Maxi Bax, Wonder Woamn |
Parins | 2050 | Ameli Steifer, Harry Barry |



I managed to get with this SQL

SELECT p.name, p.year,
GROUP_CONCAT(CONCAT(m.title, " " ,m.surname," ", m.lastname) SEPARATOR ', ')
AS members FROM places p INNER JOIN member m on p.id = m.idPlace
GROUP BY m.idPlace


the following result


name | year | members |
----------------------------------------------
Berlin | 2011 | Dr Maxi Bax, Wonder Woamn |
Parins | 2050 | Ameli Steifer, Harry Barry |



which produces an extra whitespace before Wonder Woman and Harry Barry. Is it possible to get the result without the extra whitespace?

Answer

It's because of the space in your CONCAT function. Both Wonder Woamn and Harry Barry do not have a title, therefore the CONCAT function method returns [emtpy-string][space]Wonder[space][Woamn]. When you combine this with your GROUP_CONCAT, which has a trailing space, it appears as two spaces.

One solution to this problem is to return the space with the title, if and only if the title is not null.

CONCAT(IF(m.title IS NULL, NULL, CONCAT(m.title, " ")))

A similar issue can exist with your other columns as well. You could, alternatively, run a REGEXP search/replace to replace any extra spaces, but that's a whole another scenario and I would advise against it.

Comments