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.

Development Tools Database Foundations SQL Calculating, Aggregating and Other Functions Grouping, Joining and Cleaning Up

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

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;

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

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

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

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.

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