Why does my SQL SELECT query output only 1 result-CodePudding

The select function would sometumes output 1 result. Does anyone know why?

SELECT * FROM people 
WHERE id = (SELECT person_id FROM stars
            WHERE movie_id = (SELECT id FROM movies
                              WHERE year = 2004))
ORDER BY birth;

CodePudding user response：

You should avoid subqueries. You're best bet is to use something like the following code:

SELECT 
    ppl.* -- to get just people information
FROM 
    people ppl, 
    stars sta, 
    movies mov
WHERE 
    ppl.id = sta.person_id
    AND sta.movie_id = mov.id
    AND mov.YEAR = 2004
ORDER BY 
    ppl.birth;

If you want to have stars information or movie information you just need to add the desired fields on the return like mov.title (assuming you have a column named title on movies table :P)

EDIT: As pointed out, I will leave an example using JOIN also.

SELECT 
    ppl.* -- to get just people information
FROM 
    people AS ppl
INNER JOIN
    stars AS sta ON ppl.id = sta.person_id
INNER JOIN
    movies AS mov ON sta.movie_id = mov.id
WHERE
    mov.YEAR = 2004;