title: MYSQL array aggregate function like PostgreSQL array_agg
body: <p>I got two tables on MYSQL, I wonder if there is any aggregate function on MYSQL as array_agg() FROM postgreSQL.</p>
  
  <p>TABLE 1 properties Only have 8 records
  TABLE 2 records who captured the property, so sometimes can be 1 or n times for the same property, and I got this Qry:</p>
  
  <pre><code>SELECT p.id, pcb.users_admin_id as uid
  FROM properties p
  INNER JOIN prop_captured_by pcb ON p.id = pcb.property_id
  -- GROUP BY p.id
  
  id    uid
  200   1
  200   80
  202   1
  202   80
  211   1
  211   10
  211   81
  215   10 ...
  </code></pre>
  
  <p>If I use the GROUP BY part I get this:</p>
  
  <pre><code>id    uid
  200   1
  202   1
  211   1
  215   10 ...
  </code></pre>
  
  <p>Losing any other data than the first value for users_admin_id. I know that I can achieve my desired result with array_agg() function from postgreSQL but I can't figured out how to do it on MYSQL.</p>
  
  <p>This is my desire Result:</p>
  
  <pre><code>id    uid
  200   1,80 //an array, I don't mind about the separator, could be anything.
  202   1,80
  211   1,10,81
  215   10 ...
  </code></pre>
  
  <p>I have tried UNION, GROUP BY, INNER JOIN... no luck...
  Any pointers?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I am using the <a href="http://www.tonymarston.net/php-mysql/many-to-many.html" rel="noreferrer">many to many relations</a> from this guy. Hopes it is useful for someone else. I needed to add the user's name from a third table, so the final query looks like this:</p>
  
  <pre><code>SELECT p.id, group_concat(pcb.users_admin_id) as uid, group_concat(ua.name) as uin
  FROM properties p
  INNER JOIN prop_captured_by pcb ON p.id = pcb.property_id
  INNER JOIN users_admin ua ON ua.id = pcb.users_admin_id
  group by p.id;
  </code></pre>