title: Using GROUP_CONCAT on subquery in MySQL
body: <p>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).</p>
  
  <p>Basically, I do something like this:</p>
  
  <pre><code>SELECT *,
  GROUP_CONCAT((SELECT userid FROM favourites WHERE itemid = items.id) SEPARATOR ',') AS idlist
  FROM items
  WHERE id = $someid
  </code></pre>
  
  <p>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:</p>
  
  <blockquote>
    <p><em>1242 - Subquery returns more than 1 row</em> </p>
  </blockquote>
  
  <p>I thought that was kind of the point of using <code>GROUP_CONCAT</code> instead of, for example, <code>CONCAT</code>? Am I going about this the wrong way?</p>
  
  <hr>
  
  <p>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?</p>
  
  <p>Thank you!</p>
  
  <p>Edit: I just tried to do this:</p>
  
  <pre><code>SELECT [...] LEFT JOIN favourites ON (userid = itemid OR creator = userid)
  </code></pre>
  
  <p>And <strong>idlist</strong> is empty. Note that if I use <code>INNER JOIN</code> instead of <code>LEFT JOIN</code> I get an empty result. Even though I am sure there are rows that meet the ON requirement.</p>