How to get name of comma separated id in mysql?

Question

I am using two tables -institute and category. Institute table is having category id with comma separated values like 1,2. I want to get these comma separated values as IT,Health in MySQL query result.

I am using this below query:

SELECT i.*, c.category_name FROM institute i, category c WHERE FIND_IN_SET(c.category_id, i.institute_category)

Which shows me the below result :

I am looking for comma separated values in categories and dont need duplicate institute id i.e.

Normalize your database. http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — chris85, Oct 28 '15 at 05:37
Here is complete description to know what you are doing [link](https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) — Rizwan Taj, Oct 28 '15 at 05:38

Sumit Pandey · Accepted Answer · 2015-10-28T05:36:25.783

6

For getting your expected results:

This is what your query will look like (Using GROUP BY AND GROUP_CONCAT)

SELECT i.*, GROUP_CONCAT(c.category_name) FROM institute i, category c 
WHERE FIND_IN_SET(c.category_id, i.institute_category) 
GROUP BY i.institute_category

edited Oct 28 '15 at 05:36

answered Oct 28 '15 at 05:29

Sumit Pandey

448
2
9

can I apply group_concat and group by more than one column? – deepak bhardwaj Oct 28 '15 at 05:38
yes you can apply group_concat on multiple columns, but separately – Sumit Pandey Oct 28 '15 at 05:39

score 0 · Answer 2 · edited Oct 28 '15 at 05:34

0

Use group_concat function:

SELECT i.*, group_concat(c.category_name) FROM institute i, category c
WHERE FIND_IN_SET(c.category_id, i.institute_category)

edited Oct 28 '15 at 05:34

Narendrasingh Sisodia

21,247
6
47
54

answered Oct 28 '15 at 05:32

Ravneet

300
1
5

How to get name of comma separated id in mysql?

2 Answers2