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
David Endersby
24,915 PointsMySQL Grouping, Joining and Clearing up Challenge #3
What am I missing: Like before, group by "movie_id" on reviews, select average "score" as "average", filter out averages over 2 and do an outer join on the movies table. Bring back the movie "title" first then the average. Clean up the average and set it to 0 if null.
SELECT IFNULL(AVG(score).0) AS average FROM reviews OUTER JOIN movies ON movies.title = reviews.average GROUP BY movie_id HAVING average < 2;
2 Answers
Colin Marshall
32,861 PointsI just did this one last night. Took me quite a few tries.
You're missing the movies.title from the SELECT part, there should be a comma instead of a period in the IFNULL, RIGHT is missing from the front of OUTER JOIN, and your OUTER JOIN should be ON movies.id with reviews.movie_id.
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;
Separated out on multiple lines:
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;
David Endersby
24,915 PointsThank you! :-)