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

PHP

Posted by Chris McCall
Chris McCall
12,796 Points

SQL grouping Joining and Cleaning up

I'm having a hard time on step 3 of this challenge: Database Foundations - SQL Calculating, Aggregating and Other Functions - Gouping Joining and Cleaning up CODE CHALLENGE number 2

Here's what I have: SELECT IFNULL(AVG(score),0) AS average FROM reviews LEFT OUTER JOIN movies ON movies.title = reviews.average GROUP BY movie_id HAVING average <= 2;

I can't seem to figure out what is wrong. Can anyone help?

2 Answers

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

Do you want to check?

ON movies.title = reviews.average
Chris McCall
Chris McCall
12,796 Points

I'm not sure which part is wrong, but I'm assuming it's that line.

Andrew Shook
Andrew Shook
31,709 Points
Andrew Shook
Andrew Shook
31,709 Points

Well the question in that challenge asks you: "Like before, group reviews by "movie_id", get the average "score" as "average" and filter out any averages over 2." So what you need to do is write a query that selects the scores from the reviews table and group them according their movie_id. Then filter out all of the scores that are greater than 2. Your query selects scores and set them to 0 if they are null. Then you alias the scores and average. Next you join the reviews table and movies table where you set movie titles equal to review averages. I think that is where your problem is coming from. I just redid this challenge to double check the question, and I didn't have to do a left outer join. so If you remove everything from "left outer join" to "group by" in your answer you should be doing a lot better.

Chris McCall
Chris McCall
12,796 Points

The question is explicitly asking for an outer join. Is it incorrect?

Andrew Shook
Andrew Shook
31,709 Points

When i did it just a few minutes ago I didn't have to do a left outer join. I just did:

SELECT AVG(score) AS average FROM reviews GROUP BY movie_id HAVING average >=2;
Chris McCall
Chris McCall
12,796 Points

Hmm...Just tried that and it's asking for an outer join.