Why this code doesn't work? It should output 19.325 rows. I only get 4 rows. Meanwhile, it worked well when I used JOIN
SELECT DISTINCT name
FROM people
WHERE id IN
(
SELECT person_id
FROM stars
WHERE movie_id =
(
SELECT id
FROM movies
WHERE year = 2004
)
);
As Bart mentioned in the comment above the problem is in strict filter on movie_id:
WHERE movie_id IN ( SELECT id FROM movies WHERE year = 2004 ) );
I know you mentioned JOINs, so you are aware of them. But just for the reference:
SELECT DISTINCT name
FROM people
JOIN stars ON stars.person_id = people.id
JOIN movies ON movies.id = stars.movie_id
WHERE movies.year = 2004
;