CODEWITHSUNDEEP
sqljoinforeign-keyssql-server-2017
Mihael Keehl
Mihael Keehl

Reputation: 355

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

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!

Upvotes: 0

Views: 1118

Answers (2)

NaDeR Star
NaDeR Star

Reputation: 655

Let Check 'JOHNNY CAGE'

SELECT Actor.FirstName, Actor.LastName, COUNT(*) As AllPlayed
        FROM Film
        JOIN Film_Actor on Film_Actor.FilmId = Film.FilmId
        JOIN Actor ON Actor.ActorId = Film_Actor.ActorId
    Where FirstName ='JOHNNY' And LastName = 'CAGE'
    GROUP BY Actor.FirstName, Actor.LastName

Result : JOHNNY CAGE 29

Johny was at 29 films at all, nor in 64 Action films

Then your query s wrong, Try changing 

JOIN Film on Film.FilmId = Film_Category.CategoryId

with

JOIN Film on Film.FilmId = Film_Category.FilmId

....

SELECT Actor.FirstName, Actor.LastName, COUNT(*)
FROM Category JOIN Film_Category ON Category.CategoryId = Film_Category.CategoryId
              JOIN Film on Film.FilmId = Film_Category.FilmId
              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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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.

Upvotes: 2

Related Questions