sql query: To Select multiple value of second table in comma seperated form [closed]

sql query to get multiple value of second table in comma seperated form. both table are joined by tbl 1 id.

tbl 1
   id  category
   1    cat 1
   2    cat 2

  tbl 2
  id   tbl_1_id subcategory    
  1     1        sub 1   
  2     1        sub 2 
  3     1        sub 3
  4     2        sub 4



query result expected
1    cat 1  sub 1,sub 2,sub 3,   
2    cat 2  sub 4   

Answer

In mYsql you can try this (using GROUP BY and GROUP_CONCAT function). GROUP_CONCAT() concatenate the fields with same TABLE_1_ID and CATEGORY name – the fields listed after GROUP BY – , using a “,” as a separator.

SELECT A.TABLE_1_ID, B.CATEGORY, GROUP_CONCAT(A.SUBCATEGORY) AS SUBC
FROM SUBCATEGORIES A
INNER JOIN CATEGORIES B
GROUP BY A.TABLE_1_ID, B.CATEGORY;