MySQL: combining multiple values after a join into one result column
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`;
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`;The error you got was because the a was missing after the subquery. The subquery also needed to be fixed, to include publication_id in the select and a group by clause.