Is it possible with such a DB scheme to implement a query that displays the number of Thrillers for each studio, and if the studio does not have thrillers, do you need to output 0?

DB schema

SELECT Studios.StudioName, Count(Genres.GenreName) AS [Count-GenreName] FROM ((Genres INNER JOIN FilmsGenres ON FilmsGenres.GenreID=Genres.GenreID) INNER JOIN Films ON FilmsGenres.FilmID=Films.FilmID) INNER JOIN Studios ON Films.StudioID=Studios.StudioID GROUP BY Studios.StudioName, Genres.GenreName HAVING (((Genres.GenreName)='Thriller'));

With this code, managed to get the following result:

 [StudioName] [Count-GenreName] [20 Century Fox] [1] [Syncopy] [2]

And you need to get:

 [StudioName] [Count-GenreName] [20 Century Fox] [1] [Syncopy] [2] [Universal Studios] [0] [Quad] [0]

I tried to use LEFT OUTER JOIN or RIGHT OUTER JOIN, but it did not lead to the result.

  • Why do you need the FilmsGenres table, is it not easier to store the id of the genre in the Films table? - haswell
  • @Haswell obviously, the film can have several genres at the same time, for example, "fantasy" and "comedy." - Yaant September
  • @Yaant, I already understood. For me, films have one genre) either comedy or horror) - haswell
  • FROM studios LEFT JOIN films LEFT JOIN filmsgenres LEFT JOIN genres - Akina
  • 2
    if you specifically use your line : facepalm: I specify the order of binding tables! And I do not give the final text of the request - it is assumed that you understand what you are doing and will easily bring the request to a working state. - Akina

1 answer 1

Indeed, with JOIN to the Studios table, we get the result without 0.

 SELECT Studios.StudioName, (SELECT COUNT(*) FROM Films LEFT JOIN FilmsGenres ON FilmsGenres.FilmID=Films.FilmID LEFT JOIN Genres ON Genres.GenreID=FilmsGenres.GenreID WHERE Films.StudioID=Studios.StudioID AND Genres.GenreName='Thriller') AS total FROM Studios;

This request is working. Checked personally. It does not work very fast. Maybe someone will offer a faster option.

For a small acceleration, you can not attach Genres and in the condition set FilmsGenres.GenreID=X (where X is the number of the genre thriller)