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

Question

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 1

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.