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
Tong Vang
9,926 PointsChallenge Engine???
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 its "id" column and display the movie "title" before the "average". Finally, filter out any "average" score over 2.
My answer:
SELECT movies.title, IFNULL (AVG(score).0) AS average FROM reviews LEFT OUTER JOIN movies ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average < 2;
I'm getting missing IFNULL statement. Is there something wrong with the challenge engine or my coding? I can clearly see the IFNULL statement. This is not the first time It has asked for things which are present. I've gotten to the point I don't trust the engine when it returns this type of statements.
9 Answers
Philip Bradbury
33,368 PointsTry deleting the space between the IFNULL and the argument.
SELECT movies.title, IFNULL(AVG(score).0) AS average FROM reviews LEFT OUTER JOIN movies ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average < 2;
I cant remember where the actual challenge is to test it
Tong Vang
9,926 PointsDid that. Same thing happened.
Philip Bradbury
33,368 PointsLink to the actual challenge so i can have a proper look
Tong Vang
9,926 Pointshttp://teamtreehouse.com/library/grouping-joining-and-cleaning-up
Its the third challenge.
Philip Bradbury
33,368 PointsThe period in the IFNULL(AVG(score).0) should be a comma IFNULL(AVG(score),0)
Tong Vang
9,926 PointsThanks.
Tong Vang
9,926 PointsIts still saying something is wrong.
Philip Bradbury
33,368 PointsSELECT title, IFNULL(AVG(score),0) AS average FROM movies RIGHT OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average < 2;
Philip Bradbury
33,368 PointsMight be LEFT OUTER JOIN cant remember... try both
Tong Vang
9,926 PointsThe solution was "FROM movies" not "FROM reviews". Where did it mentioned "FROM movies" in the directions? It said "FROM reviews". Crazy...