List the name of all the directors who have not directed a movie since a certain year

I’m trying to learn PostgreSQL with the imdb database and I can’t seem to figure out how to list the directors who have not directed a movie since a particular year.

I have three tables to work with

1. Table movie with mov_id, mov_title, mov_year
2. Table director with dir_id, dir_name
3. Table movie_direction with dir_id and movie_id

I tried the code below to list all the directors who have not directed a movie since 1988, and this isn’t working. Can someone guide me to the right direction on how to achieve this?

from movie_direction
join director d using (dir_id)
join movie m using (mov_id)
where m.mov_year < 1988

Answer

Use aggregation:

select d.dir_name
from movie_direction md join
     director d
     using (dir_id) join
     movie m
     using (mov_id)
group by dir_id, d.dir_name
having max(m.mov_year) < 1988;

Note that this aggregates by dir_id, just in case two directors have the same name.