How to Join Tables with Comma-Separated Join Fields in SQL?

Joining Tables with Comma-Separated Join Fields

When working with tables containing comma-separated lists, it becomes challenging to perform joins using conventional methods. In this case, we encounter two tables, "categories" and "movies," where the "categories" column in the "movies" table holds multiple category IDs separated by commas.

To achieve a join that retrieves the corresponding category names instead of the raw IDs in the "categories" column, we can leverage the following SQL 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

This query employs the find_in_set() function to determine if a category ID specified in the "categories" table exists within the comma-separated list stored in the "m.categories" field. By iteratively checking each candidate category from the "categories" table against the "m.categories" value, the query identifies matching categories.

The group_concat() function is then used to aggregate the matching category names into an array, concatenating them into a single string field for each row in the result set. Finally, the query groups the results by the "m.id" column, ensuring that each movie ID appears only once in the output.

As a result, this query efficiently replaces the comma-separated category IDs in the "movies" table with the corresponding category names, effectively performing a join operation on tables with non-traditional join fields.

The above is the detailed content of How to Join Tables with Comma-Separated Join Fields in SQL?. For more information, please follow other related articles on the PHP Chinese website!