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

Posted by Unsubscribed User
Unsubscribed User
7,273 Points

How can I tell what is wrong with my SQL query, when the statement is parsing?

Hi everyone!

I have a SQL statement which, it appears to me, matches all the requirements of the question. The statement is validating and giving output. But my answer is not correct. How am I able to tell what is wrong with my statement, without more information from the SQL console?

Edit: Seems the 'add my code' option didn't add my code... :p

Here's my SQL statement: 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

And here's the question: 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, include averages under 2.

Link: https://teamtreehouse.com/library/database-foundations/sql-calculating-aggregating-and-other-functions/grouping-joining-and-cleaning-up-2

Thanks!

Sven Harder
Sven Harder
14,943 Points
Sven Harder
Sven Harder
14,943 Points

Hi, the following statement should work.

SELECT title, IFNULL(AVG(score),0) AS average FROM movies LEFT OUTER JOIN reviews ON reviews.movie_id = movies.id GROUP BY movie_id HAVING average < 2

1) you forgot to include the smaller than 2 on the HAVING statement.

2)you need to turn the movies table and the reviews table while joining the tables. In your version, you would get all reviews - even if there is no matching id in the movies table - which doesn't make sense. But we want to list ALL movies, even if there is no matching movie_id in the reviews table.

I hope this helps you.

Unsubscribed User
Unsubscribed User
7,273 Points

Hi Sven

Thank you for your comment, it makes sense that the LEFT OUTER JOIN statement wouldn't work because I'm not correctly matching the available movie ids with their reviews.

I really appreciate your help!

1 Answer

Steven Parker
Steven Parker
231,236 Points
Steven Parker
Steven Parker
231,236 Points

I'm guessing you wrote HAVING average < 2, but without blockquoting it, the end got eaten.

So the only problem is that your result set is constrained by the reviews. But the purpose of the IFNULL is to allow titles with no reviews to be listed, which means that you'd want a RIGHT join with the movies. As Sven pointed out, you want to list ALL movies, even if there is no matching movie_id in the reviews table.

:warning: SPOILER ALERT

SELECT title, IFNULL(avg(score),0) AS average FROM reviews RIGHT OUTER JOIN movies ON reviews.movie_id = movies.id GROUP BY movie_id HAVING average < 2;
Unsubscribed User
Unsubscribed User
7,273 Points

Thank you Steven, that explains the solution quite well, and the SQL statement worked. I really appreciate your help and input!