MySQL: combining multiple values after a join into one result column

group-concat, left-join, mysql, sql

Solution

You need to group by all of the non-aggregated columns in the SELECT (and explicitly, not group by the author id, because author is part of the GROUP_CONCAT):

SELECT p.`id`, p.`title`, GROUP_CONCAT(a.`fullname` separator ', ')
from `publications` p 
LEFT JOIN `authors` a on a.`publication_id` = p.`id` 
GROUP BY p.`id`, p.`title`;

Problem

I have a database with a table for publications, each of which can have multiple authors that are stored in a different table. I'd like to query the database into giving me a list of publication titles in one column, and the combined authors for that publication in the second. ``` SELECT p.`id`, p.`title`, a.`fullname` from `publications` p LEFT JOIN `authors` a on a.`publication_id` = p.`id`; ``` This of course gives me multiple times the publication title for as many authors. ``` id title fullname -- ----- -------- 1 Beneath the Skin Sean French 1 Beneath the Skin Nicci Gerrard 2 The Talisman Stephen King 2 The Talisman Peter Straub ``` Grouping on id gives me one author per title: ``` SELECT p.`id`, p.`title`, a.`fullname` from `publications` p LEFT JOIN `authors` a on a.`publication_id` = p.`id` GROUP BY a.`id`; id title fullname -- ----- -------- 1 Beneath the Skin Sean French 2 The Talisman Stephen King ``` The result I'm looking for is this: ``` id title fullname -- ----- -------- 1 Beneath the Skin Sean French, Nicci Gerrard 2 The Talisman Stephen King, Peter Straub ``` I think the answer should be found in using GROUP_CONCAT, but the only result I'm able to get is one result row with all authors: ``` SELECT p.`id`, p.`title`, GROUP_CONCAT(a.`fullname`) from `publications` p LEFT JOIN `authors` a on a.`publication_id` = p.`id` GROUP BY a.`id`; id title fullname -- ----- -------- 1 Beneath the Skin Sean French, Nicci Gerrard, Stephen King, Peter Straub ``` And using GROUP_CONCAT after the join gives me an "Every derived table must have its own alias" error. ``` SELECT p.`id`, p.`title`, a.`fullname` FROM `publications` p LEFT JOIN (SELECT GROUP_CONCAT(a.`fullname`) FROM `authors` a) ON a.`publication_id` = p.`id`; ``` Any clues?

Original source