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!

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

Tong Vang
Tong Vang
9,926 Points
Posted by Tong Vang
Tong Vang
9,926 Points

Challenge 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
Philip Bradbury
33,368 Points
Philip Bradbury
Philip Bradbury
33,368 Points

Try 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
Tong Vang
9,926 Points
Tong Vang
Tong Vang
9,926 Points

Did that. Same thing happened.

Philip Bradbury
Philip Bradbury
33,368 Points
Philip Bradbury
Philip Bradbury
33,368 Points

Link to the actual challenge so i can have a proper look

Tong Vang
Tong Vang
9,926 Points
Tong Vang
Tong Vang
9,926 Points 
http://teamtreehouse.com/library/grouping-joining-and-cleaning-up

Its the third challenge.

Philip Bradbury
Philip Bradbury
33,368 Points
Philip Bradbury
Philip Bradbury
33,368 Points

The period in the IFNULL(AVG(score).0) should be a comma IFNULL(AVG(score),0)

Tong Vang
Tong Vang
9,926 Points
Tong Vang
Tong Vang
9,926 Points

Its still saying something is wrong.

Philip Bradbury
Philip Bradbury
33,368 Points
Philip Bradbury
Philip Bradbury
33,368 Points

SELECT 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
Philip Bradbury
33,368 Points
Philip Bradbury
Philip Bradbury
33,368 Points

Might be LEFT OUTER JOIN cant remember... try both

Tong Vang
Tong Vang
9,926 Points
Tong Vang
Tong Vang
9,926 Points

The solution was "FROM movies" not "FROM reviews". Where did it mentioned "FROM movies" in the directions? It said "FROM reviews". Crazy...