The following are the tables in the database (removed irrelevant tables). I want to query for the total count of rentals for each genre. Note that a movie can have multiple genres.

MOVIE:  Table of movies that are available in the video store.
    id:             Integer identifying the movie instance.
    title:          String with the title of the movie.
    duration:       Integer with the total duration of the movie, in seconds.
    release_year:   Integer with the year of release of the movie.
    rental_rate:    Float with the daily cost of renting the movie.
    rating:         Float in the interval 0-10, measuring how much viewers 
                    enjoyed the movie.

GENRE:  Table of movie genres.
    id:             Integer identifying the genre instance.
    name:           String with the name of the genre.

RENTAL:  Table of video rentals.
    id:             Integer identifying the rental instance.
    customer_id:    Integer identifying the customer who performed the rental.
    movie_id:       Integer identifying the movie that was rented.
    checkout_date:  Integer with unix timestamp, in seconds, marking when 
                    the video was checked out by the customer.
    return_date:    Integer with unix timestamp, in seconds, marking when the 
                    video was returned by the customer.
    cost:           Float with total dollar amount that was charged to the 
                    customer for the rental.

MOVIE_GENRE:  Association between a movie and its genre.
    movie_id:       Integer identifying the movie instance.
    genre_id:       Integer identifying the genre instance.

What I have managed to do is simply count all the movies in each genre.

SELECT COUNT(mg.genre_id) AS movieCount, g.name
  WHERE mg.genre_id = g.id
  GROUP BY mg.genre_id;

I'm doubting that this query even helps in what I'm trying to do. I know how I can achieve this programmatically, but I'm not sure how to express it in SQL. We need to map each rental to a movie then to its genres, and count the genres we find here. How do I express this in SQL?


I've reduced a bit your table schema:

create table movie 
    id int primary key, 
    title varchar(20)

create table genre
    id int primary key, 
    name varchar(20)

create table movie_genre
    movie_id int, 
    genre_id int,
    primary key (movie_id, genre_id),
    foreign key (movie_id) references movie (id),
    foreign key (genre_id) references genre (id)

create table rental 
    id int primary key, 
    movie_id int

And then insert some values:

insert into movie values 
(1, 'movie 1'), (2, 'movie 2'), (3, 'movie 3'), (4, 'movie 4');

insert into genre values 
(1, 'genre 1'), (2, 'genre 2'), (3, 'genre 3'), (4, 'genre 4');

insert into movie_genre values
(1, 2), (1, 3),
(2, 1), (2, 2), (2, 4),
(3, 1), (3, 2), (3, 3), (3, 4),
(4, 2);

insert into rental values
(1, 1), (2, 2), (3, 1), (4, 3), (5, 1), (6, 3), (7, 4);

Now joining all tables and grouping by genre.name you can get the count of each genre:

select   g.name, count(*) rentals
from     rental r
join     movie  m
on       r.movie_id = m.id
join     movie_genre mg
on       mg.movie_id = m.id
join     genre g
on       mg.genre_id = g.id
group by g.name;
name    | rentals
:------ | ------:
genre 1 |       3
genre 2 |       7
genre 3 |       5
genre 4 |       3

dbfiddle here

  • 1
    g.id is better for grouping than g.name
    – Kondybas
    Apr 1 '18 at 14:11
SELECT COUNT(g.id) AS movieCount, g.name
  FROM rental r, movie m, movie_genre mg, genre g
  WHERE r.movie_id = m.id AND m.id = mg.movie_id AND mg.genre_id = g.id
  GROUP BY g.id
  HAVING movieCount >= 20
  ORDER BY movieCount DESC;
  • You have to use JOIN..ON instead of JOIN..WHERE. Your approach generate the product size of r * m * mg * g while JOIN..ON's product is not bigger than r
    – Kondybas
    Apr 1 '18 at 14:05
  • @Kondybas, while explicit ansi join is preferred over the older "," join you dont have to use it. You can filter in the where clause instead and let the optimizer decide how to evaluate the query. How do you conclude that the JOIN is not bigger than r? For example if a movie belongs to several genres, the cardinality of the join may be bigger than r.
    – Lennart
    Apr 1 '18 at 14:36
  • Yep, product is not bigger than r*g
    – Kondybas
    Apr 1 '18 at 14:42
  • Optimizer can't do all the job. Straight explicit syntax even with USE INDEX FOR JOIN is preferrable in most cases.
    – Kondybas
    Apr 1 '18 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.