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 Joshua Moore
Joshua Moore
8,013 Points

mySQL Grouping, Joining, & Cleaning Up

Question: 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.

I think my issue might be in the joining part, but I have no idea.

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

Joshua Moore
Joshua Moore
8,013 Points

Ignore the spaces in "movies.id" and "reviews.movie_id". Error from me copying from the challenge editor. I tried fixing them and it still says wrong

1 Answer

Andrew Chalkley
STAFF
Andrew Chalkley
Treehouse Guest Teacher
Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher

I guess the question isn't that clear but what 'do an outer join on the "movies" table' means to bring back all movies. The ifnull is used to weed out any nulls from any movies that don't have reviews yet. So your query isn't actually bringing back all movies, just all reviews.

Joshua Moore
Joshua Moore
8,013 Points

This is getting embarassing, but I'm still stuck. Either I'm overlooking something simple or maybe there's a key word from an earlier lesson I need?

Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher
Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher

You need to do a RIGHT OUTER JOIN since movies is on the right hand side. This means that it will bring back all movies regardless if they have a review or not.

Joshua Moore
Joshua Moore
8,013 Points

I tried that before, but it still gave me an error. I suppose I had something else wrong, though, because I just went back and tried again and it worked. I got stuck on one a few challenges back because I was missing a comma, so I don't know. Thanks for the help though :)