0

I wrote these two codes for SQL pset7 but I'm getting different answers. Can anybody tell me the cause of the difference?

SELECT COUNT(title) FROM movies WHERE id = (SELECT movie_id FROM ratings WHERE rating = 10.0)


SELECT COUNT(id = (SELECT movie_id FROM ratings WHERE rating = 10.0)) FROM movies
Improve this question

1 Answer 1

Reset to default
0

For your first, you are counting the titles that have a specific id. Even though you are receiving a set of ids from the nested query, only one can be assigned during the WHERE clause with a =.

For the second, you are trying to perform an SQL query inside of a COUNT function, which is not possible, so it is returning the count of all of the ids within the movies table.

A nested query is probably not your best choice here. Instead, I would look into other ways to combine the two tables. :)

Improve this answer
2
  • 1
    Try " SELECT xxx Where yyy IN (Select....)
    – Cliff B
    Commented Jan 8, 2021 at 1:30
  • I would have never tried it that way, but that is a good option. My alternative was to implement a JOIN, which is the method that I used.
    – Robert S. Pratt
    Commented Jan 8, 2021 at 6:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .