Ilia Valchenko Ilia Valchenko - 1 month ago 6
SQL Question

How to get number of rows from the another table which math to the first table?

I have the following tables in my database:


  • Films
    (id, name)

  • Awards
    (id, name)

  • Films_has_Awards
    (film_id, awd_id)



How can I get the number of awards for each films?

This is my SQL query:

SELECT
Films.name,
(SELECT COUNT(*)
FROM Films
JOIN Films_has_Awards ON Films_has_Awards.film_id = Films.id
JOIN Awards ON Awards.id = Films_has_Awards.awd_id) AS NumberOfAwards
FROM
Films;


But I always get the same number of awards for each film.

Answer

Your approach is fine. You need a correlation condition in the subquery:

SELECT f.name,
        (SELECT COUNT(*)
         FROM Films_has_Awards fha
         WHERE fha.file_id = f.id
-----------------------------^ "correlates" to outer query
        ) AS NumberOfAwards
FROM Films f;

Notes:

  • Table aliases make the query easier to write and to read.
  • You don't need the awards table, presumably. There is no filtering on the award type.
  • You don't need Films in the subquery.