How to join two tables using a comma-separated-list in the join field
Solution 1:
select
m.id,
group_concat(c.name)
from
movies m
join categories c on find_in_set(c.id, m.categories)
group by
m.id
The output should be something like this:
Table movies {
-id- -categories-
1 Comedy,Drama
2 Action,Drama
4 Other,Dance
}
Solution 2:
Using comma separated lists in a database field is an anti-pattern and should be avoided at all costs.
Because it is a PITA to extract those comma separated values out agian in SQL.
Instead you should add a separate link table to represent the relationship between categories and movies, like so:
Table categories
id integer auto_increment primary key
name varchar(255)
Table movies
id integer auto_increment primary key
name varchar(255)
Table movie_cat
movie_id integer foreign key references movies.id
cat_id integer foreign key references categories.id
primary key (movie_id, cat_id)
Now you can do
SELECT m.name as movie_title, GROUP_CONCAT(c.name) AS categories FROM movies m
INNER JOIN movie_cat mc ON (mc.movie_id = m.id)
INNER JOIN categories c ON (c.id = mc.cat_id)
GROUP BY m.id
Back to your question
Alternativly using your data you can do
SELECT m.name as movie_title
, CONCAT(c1.name, if(c2.name IS NULL,'',', '), ifnull(c2.name,'')) as categories
FROM movies m
LEFT JOIN categories c2 ON
(replace(substring(substring_index(m.categories, ',', 2),
length(substring_index(m.categories, ',', 2 - 1)) + 1), ',', '') = c2.id)
INNER JOIN categories c1 ON
(replace(substring(substring_index(m.categories, ',', 1),
length(substring_index(m.categories, ',', 1 - 1)) + 1), ',', '') = c1.id)
Note that the last query only works if there are 2 or fewer categories per movie.