MySQL: merging multiple values ​​after merging into a single column of results

Question

I have a database with a table for publications, each of which can have several authors, which are stored in another table. I would like to query the database, showing me a list of publication names in one column and the combined authors for this 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 several times the title of the publication for 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 by 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 

As a result, I am looking for:

 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 when using GROUP_CONCAT, but the only result I can get is a single line of results 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 connection, I get the message "Each view must have its own alias."

 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?

Answer 1

You need to group all non-aggregated columns in SELECT columns (and explicitly, not the author ID, since author.id is not on the select list):

 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`; 

Answer 2

Stuart's answer is fine. This is just to show the working version of your approach:

 SELECT p.`id`, p.`title`, a.`fullname` FROM `publications` p LEFT JOIN (SELECT publication_id, GROUP_CONCAT(a.`fullname` separator ', ') FROM `authors` a GROUP BY publication_id ) a --------^ ON a.`publication_id` = p.`id`; 

You got an error because a was missing after the subquery. The subquery also needed to be fixed by including publication_id in the select clause and group by clause.