Need some GROUP_CONCAT MySQL better understanding please

Question

The issue that I'm running into is that there are more then one list_items.item_id(there are two of them). It appears that the group concat is just lumping all of the information together and not lumping them by each individual row of the parent table. This is my first rodeo with GROUP_CONCAT. What I'm hoping to acieve is each parent item with the joins information concatenated. Any help appreciated.

SELECT list_items.item_id, sub_cats.cat_name as subcatname, 
main_cats.cat_name maincatname, main_title,fabric,other,care,item_main_cat,item_sub_cat,
GROUP_CONCAT(sub_id) as subidgrp,
GROUP_CONCAT(price) as pricegrp,
GROUP_CONCAT(item_size) as itemszgrp,
GROUP_CONCAT(item_color) as itmcolorgrp,
GROUP_CONCAT(img_name) as imgnmgrp,
GROUP_CONCAT(item_quantity) as itmqntygrp
from list_items LEFT JOIN item_size_etc ON      
list_items.item_id = item_size_etc.parent_id LEFT JOIN main_cats on     
list_items.item_main_cat=main_cats.cat_id LEFT JOIN sub_cats on 
list_items.item_sub_cat=sub_cats.cat_id where list_items.active='Y'  

Answer 1

GROUP_CONCAT() is an aggregate function, which means it expects the presence of a GROUP BY clause to function correctly. Because MySQL (unlike other RDBMS) is lenient about the GROUP BY contents, your query is syntactically valid but won't produce correct results.

You may get the desired result if you merely add GROUP BY list_items.item_id, but better would be to list all columns from the SELECT list in the GROUP BY.

SELECT
  list_items.item_id, 
  sub_cats.cat_name as subcatname, 
  main_cats.cat_name maincatname,
  main_title,
  fabric,
  other, 
  care,
  item_main_cat,
  item_sub_cat,
  GROUP_CONCAT(sub_id) as subidgrp,
  GROUP_CONCAT(price) as pricegrp,
  GROUP_CONCAT(item_size) as itemszgrp,
  GROUP_CONCAT(item_color) as itmcolorgrp,
  GROUP_CONCAT(img_name) as imgnmgrp,
  GROUP_CONCAT(item_quantity) as itmqntygrp
FROM 
  list_items
  LEFT JOIN item_size_etc ON  list_items.item_id = item_size_etc.parent_id
  LEFT JOIN main_cats on  list_items.item_main_cat=main_cats.cat_id
  LEFT JOIN sub_cats on list_items.item_sub_cat=sub_cats.cat_id 
WHERE list_items.active='Y'  
GROUP BY
list_items.item_id, 
  sub_cats.cat_name as subcatname, 
  main_cats.cat_name maincatname,
  main_title,
  fabric,
  other, 
  care,
  item_main_cat,
  item_sub_cat