sqlitejoincountwhere-clausenatural-join

SQL wrong COUNT after two NATURAL JOINs

Database

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