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 Database Foundations SQL Calculating, Aggregating and Other Functions Grouping, Joining and Cleaning Up

Roy Huang
Roy Huang
4,367 Points
Posted by Roy Huang
Roy Huang
4,367 Points

Code Challenge 3

I've tried the code in my MySQL Workbench and thought it works fine, yet unable to pass the challenge.

Could some one take a look at my code?

Question: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 its "id" column and display the movie "title" before the "average". Finally, filter out any "average" score over 2.

> 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

2 Answers

Eric Butler
Eric Butler
33,512 Points
Eric Butler
Eric Butler
33,512 Points

Hey Roy, You're really close. I think the correct query is:

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

The difference:

Since you want to filter out averages above 2, you'd only include averages less than or equal to 2 in your query. Tricky, I know.

Let us know if you got it to work.

Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

The challenge seems to require either RIGHT OUTER JOIN or LEFT OUTER JOIN

You could do

reviews RIGHT OUTER JOIN movies

or

movies LEFT OUTER JOIN reviews
Eric Butler
Eric Butler
33,512 Points
  • Edited my answer to include Jason's use of RIGHT OUTER JOIN. Hopefully that works.
ryanjones6
ryanjones6
13,797 Points
ryanjones6
ryanjones6
13,797 Points

Eric has the answer! However, for mine: it requested everything below two, Not below and equal too.

HAVING average < 2;

-- Also, you don't have to define movie or reviews for the SELECT section, since these columns are distinct between the two tables. MySQL knows better. However this is a lazy practice and it is better to go with Eric's code.

Sherrie Gossett
Sherrie Gossett
14,924 Points
Sherrie Gossett
Sherrie Gossett
14,924 Points

After GROUP BY you can just use "movie_id" instead of "reviews.movie_id", and "score" instead of "reviews.score" if you like: 

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