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
Saira Bottemuller
Courses Plus Student 1,749 PointsSQL Challenge: Grouping, Joining, & Cleaning Up
Hello! I am on Task 3 of 3 in the SQL Challenge for "Grouping, Joining, and Cleaning Up". Here is the URL: https://teamtreehouse.com/library/database-foundations/sql-calculating-aggregating-and-other-functions/grouping-joining-and-cleaning-up-2
The requirement is: "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, include averages under 2."
I will be honest. SQL was very easy for me at first, but as its gotten a little more complex, I am really starting to struggle. In this particular task, I am unsure how to "display the movie 'title' before the 'average'". Here is what I have so far, but I'm not even sure it's correct - especially where I'm trying to do the join, and I have no idea how to display 'title' before 'average':
SELECT IFNULL(AVG(score)0) AS average FROM reviews OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average < 2;
2 Answers
Jason Anello
Courses Plus Student 94,610 PointsHi Saira,
Your attempt in your comment is a little bit closer since you're using both the reviews and movies table around the OUTER JOIN
Since we need to display both the title and average score we have to make sure both of them appear in the SELECT portion of the query, placing the title first.
Also, the IFNULL function is missing a comma between its 2 arguments.
SELECT title, IFNULL(AVG(score), 0) AS average
The only other issue is the OUTER JOIN itself. You have to specify whether it's a left or right outer join and then you have to decide which side of that the movies and reviews table should be on.
The OUTER keyword is supposed to be optional but the challenge seems to be requiring it. In other words, LEFT OUTER JOIN and LEFT JOIN should be treated the same but the challenge is requiring OUTER be in there.
If you use LEFT OUTER JOIN then the table that you put on the left side of that will have all of its rows included. The table on the right won't necessarily have all of its rows included.
In this case, we want to make sure we have all the movies in the results so we should place the movies table on the left side. Keep in mind that the reviews table won't necessarily include all the movies because some may not have a review yet. This is why it can't be on the left side of a LEFT OUTER JOIN
FROM movies LEFT OUTER JOIN reviews
If you wanted to solve the task with a right outer join then the reverse logic applies. The movies table should be on the right side since the right table will have all of its rows included.
FROM reviews RIGHT OUTER JOIN movies
Either one can pass the challenge. However, during the parsing stage right outer joins will be converted to equivalent left outer joins.
I hope this helps clear it up for you. If I've confused you more then let me know and I'll try to explain it a different way.
Saira Bottemuller
Courses Plus Student 1,749 PointsOH my gosh thank you so much Jason, I cannot even tell you how much I appreciate it. I was so confused, and wasn't sure where to start fixing things. Your explanation was extremely clear and easy to follow, and I wrote the code based on your instructions, and then verified it with the code you posted, and submitted it - perfect! Thank you so much, I had come to a total standstill in my progress, and this is something I'm learning for work, so it's a BIG deal. I not only understand what code I needed, but now I have a good grasp on WHY, which is really the key. Andrew does a great job in his classes (I've taken a few!) but some of what I was doing wrong were things I'd just forgotten that he'd talked about. It was my fault. Thank you thank you!!!! :)
Jason Anello
Courses Plus Student 94,610 PointsYou're welcome. I'm glad it helped.
Sharla Kew
15,356 PointsThanks for this detailed explanation. OUTER JOIN does have to be specified, though. JOIN defaults to INNER JOIN.
Jason Anello
Courses Plus Student 94,610 PointsHi Sharla,
You're welcome.
In my answer I was saying that LEFT OUTER JOIN is equivalent to LEFT JOIN.
In an outer join, the keyword OUTER is optional but LEFT or RIGHT is required.
Sharla Kew
15,356 PointsAh you are saying that if you use LEFT or RIGHT then mysql knows that obviously you're trying to do an OUTER JOIN? I thought you were saying that JOIN on its own means OUTER JOIN. Gotcha.
Jason Anello
Courses Plus Student 94,610 PointsThat's correct. In the challenge you can't do JOIN on its own because as you mentioned it will be the same as INNER JOIN
But with LEFT or RIGHT it knows that it's an outer join.
Here's the JOIN syntax for reference: http://dev.mysql.com/doc/refman/5.7/en/join.html
Saira Bottemuller
Courses Plus Student 1,749 PointsSaira Bottemuller
Courses Plus Student 1,749 PointsI have also tried:
SELECT IFNULL(AVG(score)0) AS average FROM reviews OUTER JOIN movies ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average < 2;
both this, and the attempt posted within the original question, are resulting in the system telling me "You're missing an OUTER JOIN of some kind"...