Search code examples
sqlms-accesscsvimdb

SQL: How to separate string values separated by commas?

I'm trying to create a relational database of all the movies I have watched.

I used IMDb to rate the movies I've seen and used the site's export capability to get the data in a .csv file which I uploaded to Microsoft Access. However, the "Genre" column is a many-to-many relationship that I am hoping to turn into a one-to-many relationship.

I would like to have a table called GENRE_ID that assigns each genre a numerical ID. Then I'd have another table where each instance would have the movie ID ("const"), line item number, and GENRE_ID.

So it might look like:

const       line_item      MOVIE_ID
tt0068646       1             1   (if MOVIE_ID: 1 = "crime")
tt0068646       2             2   (if MOVIE_ID: 2 = "drama")

Here's a link to the image of my database's current state. Thank you so much for your help. This is a project I'm doing to learn more on my own time.

Solution

  • Some notes on a way to a solution.

    A table of genres

    ID  Genre
1   Action
2   Adventure
3   Thriller
4   War

    An import table

    Const   GenreList
tt00    Action, Adventure, Thriller, War

    A query

    SELECT ti.Const, ti.GenreList, tg.Genre
FROM Imports as ti, Genres as tg
WHERE ti.GenreList Like "*" & tg.Genre & "*"