SQL wrong COUNT after two NATURAL JOINs-CodePudding

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

Options:
16
14
18
20

My incorrect solution

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

COUNT(movie_id)
4803

CodePudding user response：

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'
);