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

PHP

Posted by Eldy Voon
Eldy Voon
3,812 Points

I gone mad

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.

select movie_id, title, ifnull(avg(score),0) as average from reviews left outer join movies on movies.id=reviews.id group by movie_id having average > 2

what is wrong?! the question is confusing!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

3 Answers

Chris McKnight
PLUS
Chris McKnight
Courses Plus Student 11,045 Points
Chris McKnight
Chris McKnight
Courses Plus Student 11,045 Points

You are not joining on the correct value from the reviews table. It should be movie_id instead of id. Also, I believe you want to swap your tables around in your join since you are doing a left outer join. By swapping them, you say give me all of the data in reviews with the matching movies (right). Finally, you want to filter out averages greater than 2 so you need to swap your logic so you only get averages <= 2.

select title, ifnull(avg(score),0) as average from reviews left outer join movies on reviews.movie_id=movies.id group by movie_id having average <= 2
Darius Strong
Darius Strong
3,956 Points
Darius Strong
Darius Strong
3,956 Points

WHAT AM I DOING WRONG? SELECT title, ifnull(AVG(score), 0) as average FROM reviews LEFT OUTER JOIN movies on reviews.movie_id = movies.id GROUP BY movie_id HAVING average <= 2

Chris McKnight
Chris McKnight
Courses Plus Student 11,045 Points
Chris McKnight
Chris McKnight
Courses Plus Student 11,045 Points

It seems like the challenge isn't allowing it. Challenge 2 and 3 aren't returning the correct results. One movie has an average score of 1.5. I tried the query I posted on my local MySQL installation and it works.

Juliano Vargas
PLUS
Juliano Vargas
Courses Plus Student 15,575 Points
Juliano Vargas
Juliano Vargas
Courses Plus Student 15,575 Points

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;

Chris McKnight
Chris McKnight
Courses Plus Student 11,045 Points
Chris McKnight
Chris McKnight
Courses Plus Student 11,045 Points

I see. They were expecting movies that have zero ratings to be returned as well.