PHP Lover PHP Lover - 5 days ago 5
SQL Question

SQL query using data from other table

I have a normalized table:

`Table: TheMovies`
id | MovieName
---------------------
1 | Zootopia
2 | Moana
3 | Toy Story

`Table: TheGenres`
id | GenreName
---------------------
21 | Action
22 | Animation
23 | Adventure

`Table: mMoviesGenres`
movieID | genreID
---------------------
1 | 21
1 | 23
2 | 22
2 | 23
2 | 21
3 | 23


All works fine, but I need a Query which will shoe me similiar movies based on at least one of the genres of MovieID = 1.

Can you give me an sql query so I have a basic idea of doing that, to be able to create more advanced queries?

Answer

To query using data from another table, you can join two or more tables into a single table by using JOIN clause.

SELECT TheMovies.* FROM mMoviesGenres JOIN TheMovies ON mMoviesGenres.MovieID = TheMovies.MovieID WHERE mMoviesGenres.MovieId <> 1 AND mMoviesGenres.GenreID IN (SELECT GenreID FROM mMoviesGenres WHERE MovieID = 1)

Learn more about join: Using Join to Retrieve Data from Multiple Tables

Comments