How to concatenate multiple rows of subquery results in mysql?

Question

I want to concatenate all matching data comma separately of subquery with IN() function. But this query returns only one data through join and if I am passing static ID like IN(1,2,3) then return all data corresponding to ID.

Example:

(SELECT GROUP_CONCAT(product_name SEPARATOR ", ") AS pname FROM tbl_product WHERE product_id IN (b.product_id)) as product

Returns one record:

(SELECT GROUP_CONCAT(product_name SEPARATOR ", ") AS pname FROM tbl_product WHERE product_id IN (1,2,3)) as product

Returns 3 records:

while b.product_id column containing comma separated value.

This is my query:

$this->db->select('b.*, st.description as skin_tone, sp.description as skin_profile, sc.description as skin_concerns, pua.product, pua.producttype, pua.category, ua.description as action, t.description as type,
    (SELECT GROUP_CONCAT(product_name SEPARATOR ", ") AS pname FROM tbl_product WHERE product_id IN (b.product_id)) as product', false);

$this->db->from('tbl_basket b');
$this->db->join('tbl_skin_tone st', 'st.id = b.skin_tone_id', 'LEFT');
$this->db->join('tbl_skin_profile sp', 'sp.id = b.skin_profile_id', 'LEFT');
$this->db->join('tbl_skin_concerns sc', 'sc.id = b.skin_concern_id', 'LEFT');       
$this->db->join('tbl_product_updated_all pua', 'pua.id = b.product_id', 'LEFT');
$this->db->join('tbl_user_action ua', 'ua.id = b.action_id', 'LEFT');
$this->db->join('tbl_type t', 't.id = b.type_id', 'LEFT');  
$this->db->where('b.customer_id', $reminderProductID);  

$query = $this->db->get();

return $query->result_array();

Kindly let me know where am I doing wrong.

Answer 1

This expression:

SELECT GROUP_CONCAT(product_name SEPARATOR ", ") AS pname
FROM tbl_product
WHERE product_id IN (b.product_id)) as product

Does not do what you expect. When product_id is a string, like '1,2,3', then that is one element in the in list, not three.

The correct fix to your problem is to NOT store lists as comma-separate lists. SQL has a great data structure for storing lists; it is called a "table", not a "string". In addition, storing numbers as strings is a very bad idea.

There is a work-around, while you are figuring out the right data structure. That is the functionfind_in_set():

WHERE find_in_set(product_id, b.product_id) as product