I'm working with MySQL 5.1.68.

I have a situation where I'd like to select rows from another table into a column.

enter image description here

I'd like to output this:

id # name # citizen_name (multiple citizen names)

1 # The Hague # Barack Obama, Marc Zuckerberg, George Bush

I know how to do this with multiple queries, but I'd like to do this with just one query. I've been looking at group_concat and concat_ws, but unfortunatly this hasn't brough me the result I was looking for. I've also tried to put the results from a SELECT sub query into a variable and CONCAT those results, but that didin't work either.

My SQL fiddle to show the schema and some test data: http://sqlfiddle.com/#!2/c7c7e/1.

What can I do?

1 Answer 1


You need to use GROUP_CONCAT to aggregate the citizen names first. Then use CONCAT

SELECT CONCAT(id,' # ',name,' # ',citizens) listing FROM
    SELECT A.id,A.name,GROUP_CONCAT(B.name,' ',B.surname) citizens
    FROM city A INNER JOIN citizen B
    ON A.id = B.city_id GROUP BY A.id,A.name
) AA;

Here is the SQL Fiddle to prove it : http://sqlfiddle.com/#!2/9f4b3/1

If the names become too long due to truncation, you will have to extend GROUP_CONCAT's maximum length. To set it to 16K, run the following:

SET group_concat_max_len = 1024 * 16;
SET GLOBAL group_concat_max_len = 1024 * 16;

First line is for the session, the second is for all new DB Connections.

  • 1
    Please post your request as a separate question Oct 29, 2013 at 20:45
  • My bad, didin't want to ask that after al..
    – ivodvb
    Oct 29, 2013 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.