All we need is an easy explanation of the problem, so here it is.
Given database of the website “eMovies” that manage data about cinema movies.
Write in SQL query the number of movies that belongs to comedy genre and also belongs to animation genre.
Actors ActorID PK Firstname LastName Directors DirectorID PK Firstname LastName Genres GenreID PK GenreDescr Movies MovieID PK MovieName Year ActorsMovies MovieID PK ActorID PK DirectorsMovies MovieID PK DirectorID PK GenresMovies MovieID PK GenreID PK
SELECT COUNT(GenresMovies.MovieID) FROM GenresMovies WHERE GenreID='207' and GenreID='202';
202 and 207 are the ID for animation genre and comedy.
I’m trying to solve this for couple of hours with no sucsses, my code does not work.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Something like this ought to work:
select count(1) from ( select MovieID from GenresMovies where GenreID in (207,202) group by MovieID having count(distinct GenreID) = 2 ) as t
The inner select picks movies that have 2 distinct genres among 202 and 207, the outer select count how many there are.
Since GenreID, MovieID is the primary key, there is no need for distinct:
select count(1) from ( select MovieID from GenresMovies where GenreID in (207,202) group by MovieID having count(GenreID) = 2 ) as t
There’s probably a more sophisticated solution but I think this will at least get you the answer you’re looking for.
SELECT COUNT(*) FROM (SELECT movieid, COUNT(*) FROM GenresMovies WHERE genreid IN (207,202) GROUP BY movieid HAVING COUNT(*) > 1);
You are using
and operator and one movie will have only one genre in GenresMovies Table
Select Count(*),Genremovies.MoviesID From Movies Inner join GenresMovies on Movies.MovieID= GenresMovies.MovieID where GenresMovies.GenreID in(207,202) Group By Genremovies.MoviesID
You need something closer to the following:
SELECT COUNT(Movies.MovieID) FROM Movies WHERE Movies.MovieID IN (SELECT GenresMovies.MovieID FROM GenresMovies WHERE GenreID = 202) AND Movies.MovieID IN (SELECT GenresMovies.MovieID FROM GenresMovies WHERE GenreID = 207)
The problem is that each movie/genre combination is listed separately in the
GenresMovies table, so there is no row that is in both genres.
I’m not 100 percent on the syntax that Access supports. But you need to select from the Movies table and not the
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂