The challenge is apparently straightforward: write an SQL query to list the names of all people who have directed a movie that received a rating of at least 9.0.
To get from names (which lives in the 'people' table) to rating (residing in the 'ratings' table), I have to go from 'people', to the table called 'directors', to 'movies', and finally to 'ratings'. To me, the simplest way to do this is using short series of INNER JOINs, like this:
SELECT people.name FROM people INNER JOIN directors ON people.id = directors.person_id /* keep only people also directors */ INNER JOIN movies ON directors.movie_id = movies.id /* add movies of directors */ INNER JOIN ratings ON ratings.movie_id = ratings.movie_id /* add rating of movies */ WHERE ratings.rating >= 9.0 AND (people.id IS NOT NULL) /* keep only rating above 9 and only directors with non-NULL ids */ GROUP BY directors.person_id; /* To eliminate duplicate names where it refers to same person */
However, this bugs out, and I can't understand where I'm going wrong. If I leave out the last line (GROUP BY), it runs what seems to be an infinite loop (or just something that takes very long), and if I include the last line, it appears to just hang.
Please, Wise Ones, speak now.