Number of movies that belongs to comedy genre and also belongs to animation genre

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.

The tables:

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 

My attempt:

 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.

This is the fourth part of the question. Here is part zero and part one. The order does not matter

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.

Method 1

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

Method 2

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);

Method 3

You are using

and operator and one movie will have only one genre in GenresMovies Table
Use Join

Select Count(*),Genremovies.MoviesID 
From Movies
Inner join GenresMovies on Movies.MovieID= GenresMovies.MovieID
where GenresMovies.GenreID in(207,202)
Group By Genremovies.MoviesID

Method 4

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 GenersMovies table.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply