Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
You're awesome! Great, thanks! –  Ivo van Beek Oct 29 '13 at 19:48
1  
Please post your request as a separate question –  RolandoMySQLDBA Oct 29 '13 at 20:45
    
My bad, didin't want to ask that after al.. –  Ivo van Beek Oct 29 '13 at 20:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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