Using GROUP_CONCAT on subquery in MySQL

Question

I have a MySQL query in which I want to include a list of ID's from another table. On the website, people are able to add certain items, and people can then add those items to their favourites. I basically want to get the list of ID's of people who have favourited that item (this is a bit simplified, but this is what it boils down to).

Basically, I do something like this:

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

This way, I would be able to show who favourited some item, by splitting the idlist later on to an array in PHP further on in my code, however I am getting the following MySQL error:

1242 - Subquery returns more than 1 row

I thought that was kind of the point of using GROUP_CONCAT instead of, for example, CONCAT? Am I going about this the wrong way?

---

Ok, thanks for the answers so far, that seems to work. However, there is a catch. Items are also considered to be a favourite if it was added by that user. So I would need an additional check to check if creator = userid. Can someone help me come up with a smart (and hopefully efficient) way to do this?

Thank you!

Edit: I just tried to do this:

SELECT [...] LEFT JOIN favourites ON (userid = itemid OR creator = userid)

And idlist is empty. Note that if I use INNER JOIN instead of LEFT JOIN I get an empty result. Even though I am sure there are rows that meet the ON requirement.

Answer 1

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.