Display the title and year of all non SH and non CH films that were produced in the same year as any of the SH or CH videos were produced

Question

Ok so I am having some issues with a SQL question. I have some code for the previous question and I feel that it has to be implemented with this question in some way.

SELECT m1.production_year, m1.title
FROM movie m1
WHERE EXISTS (SELECT 1
FROM movie m2
WHERE m2.genre_code IN ('SH', 'CH')
AND m1.production_year < m2.production_year);

Now, how do I exclude rows from displaying any SH and CH in the genre_code?

Answer 1

SELECT
    m1.production_year, m1.title
FROM
    movie m1
WHERE
    m1.genre_code NOT IN ('SH', 'CH')
    AND m1.production_year IN (
        SELECT m2.production_year FROM movie m2 WHERE m2.genre_code IN ('SH', 'CH')
        )