CODEWITHSUNDEEP
sqlitejoincountwhere-clausenatural-join
Tewfiq
Tewfiq

Reputation: 17

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

Upvotes: -1

Views: 65

Answers (1)

forpas
forpas

Reputation: 164174

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

Upvotes: 0

Related Questions