How to display non-null and null values from multiple tables?

My question is about how to list both null and non-null values in the results. How do you display the null values of "Creator" who made "family movies"? Should I use all 3 JOINS, add an "ON COALESCE" or am I missing something else?

Please find the SQL query below:

Select first_name, COUNT(*) AS nr_family_films
FROM Creator
JOIN MovieSeries ON MovieSeries.creator_id = Creator.creator_id
RIGHT JOIN Movie ON Movie.movie_series_id = MovieSeries.movies_series_id
WHERE MovieSeries.genre_id = 2
Group BY first_name
Order BY nr_family_films desc;

1 answer

  • answered 2020-10-29 15:37 JShark

    EDIT

    Actually I think this may be more accurate for your query - JOIN on a COALESCE to replace NULLS with '' which can be used to JOIN...

    SELECT first_name, count(*) AS nr_family_films
    FROM Creator
    JOIN MovieSeries ON COALESCE(MovieSeries.creator_ID, '') = COALESCE(Creator.Creator_ID, '')
    RIGHT JOIN Movie ON MOVIE.moview_series_id = MovieSeries.MovieSeries_ID
    WHERE MovieSeries.genre_id = 2
    GROUP BY first_name
    ORDER BY nr_family_films desc;
    


    ORIGINAL ANSWER

    The JOIN to MovieSeries needs to be a LEFT JOIN. Currently, it's an INNER JOIN, which means it will only return rows where the Creator_ID is the same in both tables (and exists in MovieSeries - NULLs will not be included).

    SELECT first_name, count(*) AS nr_family_films
    FROM Creator
    LEFT JOIN MovieSeries ON MovieSeries.creator_ID = Creator.Creator_ID
    RIGHT JOIN Movie ON MOVIE.moview_series_id = MovieSeries.MovieSeries_ID
    WHERE MovieSeries.genre_id = 2
    GROUP BY first_name
    ORDER BY nr_family_films desc;