Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Development Tools Database Foundations SQL Calculating, Aggregating and Other Functions Grouping, Joining and Cleaning Up

Saira Bottemuller
PLUS
Saira Bottemuller
Courses Plus Student 1,749 Points

SQL 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;

Saira Bottemuller
Saira Bottemuller
Courses Plus Student 1,749 Points

I 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"...

2 Answers

Hi 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
Saira Bottemuller
Courses Plus Student 1,749 Points

OH 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!!!! :)

You're welcome. I'm glad it helped.

Thanks for this detailed explanation. OUTER JOIN does have to be specified, though. JOIN defaults to INNER JOIN.

Hi 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.

Ah 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.

That'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