Querying Multiple Join Fields with Comma-Separated Values

In SQL, when dealing with tables containing comma-separated-lists (CSVs) in their fields, it can be challenging to perform joins effectively. This article explores a specific scenario where the categories column in a movies table contains multiple category IDs, and the goal is to exclude it entirely while fetching the corresponding category names from a categories table.

The following tables illustrate the scenario:

Table categories:
-id-       -name-
1          Action
2          Comedy
4          Drama
5          Dance

Table movies:
-id-       -categories-
1          2,4
2          1,4
4          3,5

To perform the desired join, we can utilize the find_in_set() function in conjunction with a group_concat() operation. Here's the query:

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

Query Explanation:

• find_in_set() checks if a specified substring exists within a string, in this case, if a category ID from the categories table is present in the CSV of the categories column in the movies table.
• The join clause links the two tables based on the find_in_set() condition.
• group_concat() concatenates all matching category names for each movie ID into a single string.
• The group by clause ensures that the results are grouped by movie ID, providing a clean and organized output.

The resulting output displays the movie IDs along with their corresponding category names in an array format. This allows for easy access to the relevant categories without the need to decode the CSV in the movies table.