How do I find output for this in sqlite? NULL-CodePudding

I have been trying to write a query using INNER JOIN and CASE WHEN with SUM to find the movieName where all personnel that are cast in the movie are not dead (all NULL then I need movieName linked to uniqueMovieID)?

I have 3 tables: Table 1: movies Schema: uniqueMovieId, movieName Table 2: cast Schema: uniqueMovieId, castId Table 3: personnel Schema: castId, yearOfDeath

If a personnel has not died there is NULL in yearOfDeath.

Output: MovieName_1 <- all cast are alive MovieName_2 <- all cast are alive .... MovieName_n <- all cast are alive

CodePudding user response：

Join the tables and group by movie.
Apply the condition in the HAVING clause with an aggregate function like MAX() or MIN() or SUM():

SELECT m.uniqueMovieId, 
       m.MovieName
FROM movies m
INNER JOIN cast c ON c.uniqueMovieId = m.uniqueMovieId
INNER JOIN personnel p ON p.castId = c.castId
GROUP BY m.uniqueMovieId
HAVING MAX(p.yearOfDeath) IS NULL;