Using GROUP_CONCAT on subquery in MySQL

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.

Solution 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.

Solution 2:

You can't access variables in the outer scope in such queries (can't use items.id there). You should rather try something like

SELECT
    items.name,
    items.color,
    CONCAT(favourites.userid) as idlist
FROM
    items
INNER JOIN favourites ON items.id = favourites.itemid
WHERE
    items.id = $someid
GROUP BY
    items.name,
    items.color;

Expand the list of fields as needed (name, color...).

Using GROUP_CONCAT on subquery in MySQL

Solution 1:

Solution 2:

Related

Recent Posts