Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools

Posted by Arthur Verschaeve
Arthur Verschaeve
20,816 Points

Don't understand challenge - SQL

Can someone give me the answer of this sql question? I really fail on it.

Like before, select the average "score" as "average", setting to 0 if null, by grouping the "movie_id" from the "reviews" table. Also, do an outer join on the "movies" table with it's "id" column and display the movie "title" before the "average". Finally filter out any "average" score over 2.

4 Answers

Ben Rubin
Ben Rubin
Courses Plus Student 14,658 Points

We're not supposed to give complete answers here, so let's break it down into pieces. Suppose you wanted the average score for each movie, along with its movie ID.

SELECT movie_id, AVG(score) AS average FROM reviews GROUP BY movie_id

Now if you want to "filter out" (exclude) any movies with an average score over 2.

SELECT movie_id, AVG(score) AS average FROM reviews GROUP BY movie_id HAVING AVG(score) <= 2

The next step is to join the 'reviews' table to the 'movies' table so that you can get the title for each movie. You could do it like this:

SELECT movies.title, AVG(reviews.score) AS average FROM reviews INNER JOIN movies ON movies.id = reviews.movie_id GROUP BY reviews.movie_id HAVING AVG(reviews.score) <= 2

But there's a problem with this. Since it's an inner join, you'll only get results for movies that have at least one review. If the movie has no reviews, it won't join to the 'reviews' table, so it won't be in the query results. The solution is to use an outer join. Since you want every movie, even if that movie has no reviews, you'll need to right outer join 'reviews' to 'movies'. The right outer join means that you'll always get every row from your right table ('movies' in this case) even if it doesn't join to any rows from the left table.

SELECT movies.title, AVG(reviews.score) AS average FROM reviews RIGHT OUTER JOIN movies ON movies.id = reviews.movie_id GROUP BY reviews.movie_id HAVING AVG(reviews.score) <= 2

The last thing to do is to treat movies that have no reviews as having an average score of 0. You can do that like this:

SELECT movies.title, IFNULL(AVG(reviews.score), 0) AS average FROM...

Since NULL is different than 0, you'll need to use the IFNULL function in your HAVING clause as well. Something like this:

... HAVING IFNULL(AVG(reviews.score), 0) <= 2
James Barnett
James Barnett
39,199 Points
James Barnett
James Barnett
39,199 Points

Ben Rubin - That's an awesome and pretty complete guide to how to go about solving that code challenge. However since, you don't know what their original issue was, you've effectively turned turned a homework problem into an in-class example which defeats the purpose of homework (e.g. code challenges).

Juliano Vargas
PLUS
Juliano Vargas
Courses Plus Student 15,575 Points
Juliano Vargas
Juliano Vargas
Courses Plus Student 15,575 Points

SELECT title, IFNULL(AVG(score),0) AS average FROM movies LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average <=2;

THAT WILL DO

Carlos Igreja
Carlos Igreja
2,828 Points

I was using reviews.id instead of reviews.movie_id. That was where my error was. I am new to this. I also did mines completely opposite of yours. It worked fine once I fixed the error. Thanks. Here is my answer if any one would like another example to study it.

select movies.title, ifnull(avg(score),0) as average from reviews right outer join movies on reviews.movie_id = movies.id group by movie_id having average < 2;

sorry for no capitals, it saves me time while trying to learn.

Ismail Qaznili
Ismail Qaznili
27,996 Points
Ismail Qaznili
Ismail Qaznili
27,996 Points

I was approaching the challenge in a manner similar to Carlos Igerja's. I must say that It takes a while wrap your head around this challenge.

Arthur Verschaeve
Arthur Verschaeve
20,816 Points
Arthur Verschaeve
Arthur Verschaeve
20,816 Points

Thank you!

Rafael Alejandro Garduño E.
Rafael Alejandro Garduño E.
4,329 Points
Rafael Alejandro Garduño E.
Rafael Alejandro Garduño E.
4,329 Points

select movies.title, IFNULL(AVG(reviews.score), 0) AS average from reviews right outer join movies on ( reviews.movie_id = movies.id) group by movie_id HAVING IFNULL(AVG(reviews.score), 0) <= 2;