SQL Join Cant seem to get the right answer?

September 3, 2022

Question 3

How many movies in the database were produced by Pixar Animation Studios?

Options:

Solution

SELECT COUNT(movie_id)
FROM productioncompanies
NATURAL JOIN movies
NATURAL JOIN productioncompanies
WHERE production_company_name = "Pixar Animation Studios"

sqlite:///TMDB.db
Done.
COUNT(movie_id)
4803

>Solution :

You should join productioncompanies to productioncompanymap.
The table movies is not needed because the details of the movies are irrelevant:

SELECT COUNT(*)
FROM productioncompanymap m NATURAL JOIN productioncompanies c
WHERE c.production_company_name = 'Pixar Animation Studios';

or, with an INNER join:

SELECT COUNT(*)
FROM productioncompanymap m INNER JOIN productioncompanies c
ON c.production_company_id = m.production_company_id
WHERE c.production_company_name = 'Pixar Animation Studios';

or, with a correlated subquery:

SELECT COUNT(*)
FROM productioncompanymap
WHERE production_company_id = (
  SELECT production_company_id 
  FROM productioncompanies 
  WHERE production_company_name = 'Pixar Animation Studios'
);