Welcome to the Treehouse Community
The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.
John Martens
8,689 PointsCant get the last code challenge
Here is my answer, I'm sure its just some small thing but I have to go to work now and don't have time to debug:
select title, ifnull(avg(score),0) as average from reviews left outer join reviews on movies.id=reviews.movie_id group by movie_id having average<2;
2 Answers
Daniel Johnson
104,132 PointsYou almost had it, but you can't join a table to itself. You had FROM reviews LEFT OUTER JOIN reviews, but it should be FROM movies LEFT OUTER JOIN reviews.
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;
Robert Kulagowski
4,954 PointsI concur that the question is misleading, because it's written poorly and makes you believe that you're supposed to retrieve the values from the reviews table.
But since the question hasn't been updated in 3 months based on feedback, I'd be surprised if it ever is.
Andrew Phythian
19,747 PointsThat was my problem, I basically had the reviews and movies tables the wrong way around as per the question.
Andrew Gay
20,893 PointsAndrew Gay
20,893 PointsThanks, I was getting really frustrated with this last challenge question.
I left out the LEFT, haha git it? (There is totally two jokes lurking around in there.)
Aaron Munoz
11,177 PointsAaron Munoz
11,177 PointsBut the question asks you to get it from the "reviews" table. I feel like the question is confusing.
Tyler Brewer
1,871 PointsTyler Brewer
1,871 PointsAaron Munoz, you could also do it with the 'reviews' table first using the RIGHT OUTER JOIN instead of the LEFT OUTER JOIN.
It would look like this:
SELECT 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;