Welcome to the Treehouse Community
Looking to learn something new?
Saira BottemullerCourses 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;
Your attempt in your comment is a little bit closer since you're using both the reviews and movies table around the
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.
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.