I'm having a little trouble with a query.
I'm building a movie database, It has 3 tables. 1 for movies (tblmovie), 1 for genres (tblgenre), and 1 to connect these 2(tblmoviegenre).
I join these 3 together, so that if I had 3 movies with 3 genre's each, I would get 9 rows.
Now I would like to be able to get a movie that contains certain genre's ( for example the one's with ID 2 and 4), this only works when I do this with one genre, because each row only has 1 genre. Anybody know a work around for this?
So if I try
SELECT tblmovie.name as moviename FROM tblmovie as m LEFT JOIN tblmoviegenre as mg on m.movieID = mg.movieID LEFT JOIN tblgenre as g on mg.genreID = g.genreID WHERE mg.genreID = 2 AND mg.genreID = 4
I get no results, because each row only has 1 genre.