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

Databases

Philomath Ingeniare
Philomath Ingeniare
12,441 Points
Posted by Philomath Ingeniare
Philomath Ingeniare
12,441 Points

Database Foundations challenge

The question: 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. The query I wrote does return results, but it says "There's something wrong". So, I am not sure which part I got wrong.

Here's my query:

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

I'm not sure if this helps but you can just do

SELECT 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

because you've already calculated the average

1 Answer

Maximiliane Quel
PLUS
Maximiliane Quel
Courses Plus Student 55,489 Points
Maximiliane Quel
Maximiliane Quel
Courses Plus Student 55,489 Points

First you need to specify that you want to select the title from the movies table. Then if you want to select from reviews you need to create a RIGHT OUTER JOIN, on the other hand if you select from movies then you can create a LEFT OUTER JOIN:

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

SELECT movies.title, IFNULL(AVG(score),0) AS average 
FROM reviews RIGHT OUTER JOIN movies ON movies.id = reviews.movie_id 
GROUP BY movie_id HAVING average < 2;
Philomath Ingeniare
Philomath Ingeniare
12,441 Points
Philomath Ingeniare
Philomath Ingeniare
12,441 Points

Thanks for the clarification, seems like the direction of the OUTER JOIN was the issue.