Anderson Anderson - 1 month ago 14
MySQL Question

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

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 Source

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;