Search code examples
sqlitejoingroup-byhaving

How do I find output for this in sqlite? NULL

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

Solution

  • 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;