SQL: Joining three tables (junction tables)

[sophryu99]

Case: Find 10 actors/actresses in the most movies.

Schemas
TITLE

ROLE

NAME

Approach

1. Join TITLE and ROLE with key tConst
2. Join NAME and ROLE with key nConst
3. GROUP BY name to count the number of movies for each actors/actress
4. ORDER BY descending value

SELECT
    n.primaryName as name,
    COUNT(t.primaryTitle) as total
FROM name n
JOIN role r
    ON n.nConst = r.nConst
JOIN title t
    ON t.tConst = r.tConst
WHERE ((r.category = "actor" OR r.category = "actress") AND t.titleType = "movie")
GROUP BY name
ORDER BY total DESC
LIMIT 10;

Output