I have a MySQL query in which I want to include a list of ID&#39;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&#39;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 &#39;,&#39;) 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:

&gt;*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.

All Mysql Error-1242 Solutions on Gang of Coders

Total of 1 Mysql Error-1242 Solutions

Using GROUP_CONCAT on subquery in MySQL