SQL JOIN on foreign key that is not equal to primary key

Question

I don't exactly know how to phrase the question properly but I'm utterly confused. We have a test-exam and one of the questions is:

Sort actors descending according to the amount of movies they have appeared in. Show only the first name, the last name and the amount of action movies they have played in. (Note: Unfortunately all actors that have appeared in action movies have all appeared in the same amount).

The correct answer is displayed as following:

SELECT 
    Actor.FirstName, Actor.LastName, COUNT(*)
FROM 
    Category 
JOIN 
    Film_Category ON Category.CategoryId = Film_Category.CategoryId
JOIN 
    Film on Film.FilmId = Film_Category.CategoryId
JOIN 
    Film_Actor on Film_Actor.FilmId = Film.FilmId
JOIN 
    Actor ON Actor.ActorId = Film_Actor.ActorId
WHERE 
    Category.Name = 'Action'
GROUP BY 
    Actor.FirstName, Actor.LastName
ORDER BY 
    COUNT(*) DESC

This query returns:

JOHNNY  CAGE    64
ROCK    DUKAKIS 64
CHRISTIAN   GABLE   64
PENELOPE    GUINESS 64
MARY    KEITEL  64
OPRAH   KILMER  64
WARREN  NOLTE   64
SANDRA  PECK    64
MENA    TEMPLE  64
LUCILLE TRACY   64

The line that confuses me is the second JOIN:

JOIN Film on Film.FilmId = Film_Category.CategoryId

What does comparing Film.FilmId to Film_Category.CategoryId do as it's not a foreign key to that table? I even wonder if the answer is correct because when I enter the query:

SELECT 
    COUNT(Category.Name), Category.Name 
FROM 
    Film_Category 
INNER JOIN 
    Category ON Category.CategoryId = Film_Category.CategoryId 
GROUP BY 
    Category.Name 
ORDER BY 
    COUNT(Category.Name);

I get:

64  Action
66  Animation
68  Documentary

So, presumably all actors appeared in all action movies? Could anybody please help me with this because I have an exam coming up very soon and I'm lost!

Answer 1

The query is not correct. Based on the ER diagram, it should be:

SELECT a.FirstName, a.LastName, COUNT(*)
FROM Category c JOIN
     Film_Category fc
     ON c.CategoryId = fc.CategoryId JOIN
     Film_Actor fa
     ON fa.FilmId = fc.FilmId JOIn
     Actor a
     ON a.ActorId = fa.ActorId
WHERE c.Name = 'Action'
GROUP BY a.FirstName, a.LastName
ORDER BY COUNT(*) DESC;

Note that the Film table is not needed. That said, I think the answer to the question is:

SELECT a.FirstName, a.LastName,
       SUM(CASE WHEN c.Name = 'Action' THEN 1 ELSE 0 END) as num_action
FROM Category c JOIN
     Film_Category fc
     ON c.CategoryId = fc.CategoryId JOIN
     Film_Actor fa
     ON fa.FilmId = fc.FilmId JOIn
     Actor a
     ON a.ActorId = fa.ActorId
GROUP BY a.FirstName, a.LastName
ORDER BY COUNT(*) DESC;

The question says to order by all films but to only show the counts of action films.