mysql - How to get All categories comma separated from this query -

i have 2 tables posts, categories. here in posts table stored category values comma separated string 5,8,23,7. while displaying posts, want show post categories comma separated flower, birds, animals. tried queries nothing helped me it. posts table example.

id  post title      categories 3   example post     5,7,23,8 

and categories table this

id   name   5    flowers 7    animals 8    birds 23   naturals 

and want result

id  post tile        category 3   example post     flowers, animals, birds 

for tried query didn't me .

select post.id, post.post_title, (select cat.name categories cat cat.id in (post.category)) categories posts post  

and returns 1 category, retrieves first category name only.

edit: updated consider fact posts.categories csv value.

you need use group_concat() function, , trick posted in sql split comma separated row in order split join csv , create output csv:

select     posts.id,     posts.post_title,     group_concat(categories.name separator ',') `category` posts inner join categories     on categories.id in (         select substring_index(substring_index(posts.categories, ',', n.n), ',', -1) value         (             select a.n + b.n * 10 + 1 n                         (select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)             ,(select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) b             order n         ) n         n.n <= 1 + (length(posts.categories) - length(replace(posts.categories, ',', '')))         order value     ) group     posts.id,     posts.post_title 

fiddle: http://sqlfiddle.com/#!9/b1ddc9/4