MySQL: combining multiple values after a join into one result column
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.
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`;