I have a big database of movies and I want to search movies by years and genres.
I have 3 table to deal with:
1. movies (Key TITLE_ID)
2. movie2genre (TITLE_ID + GENRE_ID)
3. genre (Key GENRE_ID)
movies:
+----------+---------------+------+
| TITLE_ID | TITLE | YEAR |
+----------+---------------+------+
| 10001 | Matrix | 1999 |
| 10002 | Walk on Water | 2004 |
+----------+---------------+------+
movie2genre:
+----------+----------+
| TITLE_ID | GENRE_ID |
+----------+----------+
| 10001 | 1 |
| 10001 | 2 |
| 10002 | 2 |
| 10002 | 4 |
| 10001 | 3 |
+----------+----------+
genre:
+----------+-----------+
| GENRE_ID | GENRE_TXT |
+----------+-----------+
| 1 | Action |
| 2 | Adventure |
| 3 | Thriller |
| 4 | Fantasy |
+----------+-----------+
A user sends a request for year 1999, genre Adventure or Thriller.
How can I find all movies in 1 optimized query?
Result should be:
+----------+--------+------+-----------+
| TITLE_ID | TITLE | YEAR | GENRE_TXT |
+----------+--------+------+-----------+
| 10001 | Matrix | 1999 | Action |
+----------+--------+------+-----------+
I don't care which genre gets selected, only that this movie as 1 of genres user ask for.
You can try joining together the 3 tables and then restricting the query with a WHERE clause using your requirements for year and movie genre. The query below will return only one record, which you specified would be acceptable.
SELECT m.TITLE_ID, m.TITLE, m.YEAR, g.GENRE_TXT
FROM movies m
INNER JOIN movie2genre mg ON m.TITLE_ID = mg.TITLE_ID
INNER JOIN genre g ON mg.GENRE_ID = g.GENRE_ID
WHERE m.YEAR = 1999 AND g.GENRE_TXT IN ('Adventure', 'Thriller')