MySQL Joomla GROUP_CONCAT Examples

gistfile1.sql

// SQL
SELECT
     `users`.`id`,
     `users`.`name`,
     (SELECT
             GROUP_CONCAT(`k2`.`title`)
         FROM
             `jos_k2_items` AS `k2`
         WHERE
             `k2`.`created_by` = `users`.`id`
     ) as categories
 FROM
     `jos_users` AS `users`;

// Joomla
$query = ' SELECT ' .
$this->db->nameQuote('users.id') . ', ' .
$this->db->nameQuote('users.name') . ', ' .
' (SELECT ' .
' GROUP_CONCAT(' . $this->db->nameQuote('k2.title') . ')' .
' FROM' .
$this->db->nameQuote('jos_k2_items') . ' AS ' . $this->db->nameQuote('k2') .
' WHERE ' .
$this->db->nameQuote('k2.created_by') . ' = ' . $this->db->nameQuote('users.id') .
') AS ' . $this->db->nameQuote('categories') .
' FROM ' .
$this->db->nameQuote('jos_users') . ' AS ' . $this->db->nameQuote('users');