Ilia Valchenko Ilia Valchenko - 1 year ago 54
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:

FROM Films
JOIN Films_has_Awards ON Films_has_Awards.film_id =
JOIN Awards ON = Films_has_Awards.awd_id) AS NumberOfAwards

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

Answer Source

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

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


  • 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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download