How to join two tables using a comma-separated-list in the join field

Question

I have two tables, categories and movies.

In movies table I have a column categories. That column consists of the categories that movie fits in. The categories are IDs separated by a comma.

Here's an example:

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

Table movies {
  -id-       -categories-  (and some more columns ofc)
  1          2,4
  2          1,4
  4          3,5
}

Now to the actual question: Is it possible to perform a query that excludes the categories column from the movies table, and instead selects the matching categories from the categories table and returns them in an array? Like a join, but the problem is there are multiple categories separated by comma, is it possible to do some kind of regex?

Answer 1

Brad is right; normalisation is the solution. Normalisation exists to solve this problem. It should be covered pretty well in your MySQL book if it's worth its salt.

---

If you really insist, though, you can fake the direct join by cross-matching with FIND_IN_SET (which conveniently expects a comma-delimited string of items).

Now, MySQL can't return "an array" — that's what sets of results are for — but it can give you the category names separated by, say, a pipe (|):

SELECT
       `m`.`id`,
       `m`.`name`,
       GROUP_CONCAT(`c`.`name` SEPARATOR "|") AS `cats`
  FROM
       `movies`     AS `m`,
       `categories` AS `c`
 WHERE
       FIND_IN_SET(`c`.`id`, `m`.`categories`) != 0
 GROUP BY
       `m`.`id`;

Result:

id  "name"     "cats"
---------------------------------------------------
1   "Movie 1"  "Comedy|Drama"
2   "Movie 2"  "Action|Drama"
4   "Movie 4"  "Dance"