I'm doing the pset8 tasks. Currently, I have some issues with sql7.
In 7.sql, write a SQL query to list all movies released in 2010 and their ratings, in descending order by rating. For movies with the same rating, order them alphabetically by title. Your query should output a table with two columns, one for the title of each movie and one for the rating of each movie. Movies that do not have ratings should not be included in the result.
When I do two separate queries, it works fine: request 1:
SELECT title FROM movies WHERE year = 2010
returns 11945 rows
SELECT rating FROM ratings WHERE movie_id IN (SELECT id FROM movies WHERE year = 2010)
returns 6835 rows
But when I'm trying to combine them:
SELECT title, rating FROM movies, ratings WHERE (year = 2010) AND (movie_id IN (SELECT id FROM movies WHERE year = 2010))
it returns 81644075 rows. Considering that initial db movies has only 344276 rows, it looks strange.
- What I'm doing wrong?
- Why does the combined request not work?
- Should I join tables before selecting?