3

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?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • 1
    Please post your request as a separate question Commented Oct 29, 2013 at 20:45
  • My bad, didin't want to ask that after al.. Commented 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.