title: MySql: generate random rows as a subquery
body: <p>I've got a problem with a correlation inside a sub-query.</p>
  
  <p>I have 2 tables :
  <br>- table1 : contains "groups" with a <code>groupid</code>, a <code>groupename</code> and a <code>categoryid</code>
  <br>- table2 : tells which people is member of which group (with fields: <code>userid</code>, <code>groupid</code>)</p>
  
  <p>I would like to ask my database to give me :
  <br>all groups from a specific "<code>category</code>" with for each of them :
  <br>- the <code>groupid</code>, the <code>groupname</code>
  <br>- and a random selection of 4 members for each group</p>
  
  <p>I followed the question <a href="https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast">MySQL select 10 random rows from 600K rows fast</a> to generate 4 random members of a specific group.</p>
  
  <p><br>It works well if I run the query separately.</p>
  
  <p>but if I try to incorporate my sub-query inside my "general query" :</p>
  
  <pre><code>SELECT 
  g.groupid, g.groupname, 
  (
  SELECT GROUP_CONCAT(table2.userid SEPARATOR ",") 
      FROM table2, (
          SELECT userid AS uid
          FROM table2 
          WHERE table2.groupid = g.groupid 
          ORDER BY RAND( ) 
          LIMIT 4 
      ) tmp 
  WHERE table2.userid = tmp.uid
  ) AS randomusers 
  FROM table1 AS g WHERE g.categoryid = ? 
  </code></pre>
  
  <p>... I get a <code>"Unknown column 'g.groupid' in 'where clause'" ERROR.</code></p>
  
  <p><br>I tried to pass the subquery into a <code>LEFT JOIN</code> but I can't figure out how to do it properly as each of my attempts are unsuccessful.</p>
  
  <p>Any help on this? Thanks :)</p>