Using GROUP_CONCAT on subquery in MySQL

OP almost got it right. GROUP_CONCAT should be wrapping the columns in the subquery and not the complete subquery (I’m dismissing the separator because comma is the default):

SELECT i.*,
(SELECT GROUP_CONCAT(userid) FROM favourites f WHERE f.itemid = i.id) AS idlist
FROM items i
WHERE i.id = $someid

This will yield the desired result and also means that the accepted answer is partially wrong, because you can access outer scope variables in a subquery.

Leave a Comment