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

Posted by John Martens
John Martens
8,689 Points

Cant 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
Daniel Johnson
104,132 Points
Daniel Johnson
Daniel Johnson
104,132 Points

You 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;
Andrew Gay
Andrew Gay
20,893 Points

Thanks, 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
Aaron Munoz
11,177 Points
Aaron Munoz
Aaron Munoz
11,177 Points

But the question asks you to get it from the "reviews" table. I feel like the question is confusing.

Tyler Brewer
Tyler Brewer
1,871 Points

Aaron 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;
Robert Kulagowski
Robert Kulagowski
4,954 Points
Robert Kulagowski
Robert Kulagowski
4,954 Points

I 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
Andrew Phythian
19,747 Points

That was my problem, I basically had the reviews and movies tables the wrong way around as per the question.