How to Join Tables With a Comma-Separated List of Join Fields?

Joining Tables with Comma-Separated List Join Fields

When working with databases, you may encounter scenarios where you need to join two tables based on a field containing multiple comma-separated values. In such cases, a traditional join might not suffice.

Consider the example provided, where you have a table of movies with a categories column that contains comma-separated IDs of categories. The goal is to perform a query that excludes the categories column from the movies table and instead selects the matching category names from the categories table, returning them in an array.

To accomplish this, you can utilize the following query:

SELECT m.id, GROUP_CONCAT(c.name) AS categories
FROM movies m
JOIN categories c ON FIND_IN_SET(c.id, m.categories)
GROUP BY m.id

Explanation:

The FIND_IN_SET function checks if a substring is found within a string. It returns the position of the substring if found, or 0 otherwise. In this case, it checks if the category ID from the categories table exists in the categories column of the movies table.

The query uses GROUP_CONCAT to concatenate the category names returned by the JOIN operation for each movie ID. The grouping by m.id ensures that categories are grouped together for each movie.

Output:

The output of the query should resemble the following:

| id | categories         |
|---:|-------------------|
| 1  | Comedy, Drama      |
| 2  | Action, Drama      |
| 4  | Other, Dance      |

This technique effectively joins the two tables based on the comma-separated list of categories, allowing you to retrieve the category names in an array format.

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