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

Posted by Adiv Abramson
Adiv Abramson
6,919 Points

My query has an OUTER JOIN but the editor complains that it doesn't.

I was trying to execute the following query:

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

I don't know what I am doing wrong here.

2 Answers

Sergey Podgornyy
Sergey Podgornyy
20,660 Points
Sergey Podgornyy
Sergey Podgornyy
20,660 Points

Normally you can't refer to field aliases in the WHERE clause. (Think of it as the entire SELECT including aliases, is applied after the WHERE clause.)

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

Sometimes the challenge checker misinterprets exactly what is wrong with an entry.

Steven Parker
Steven Parker
231,261 Points
Steven Parker
Steven Parker
231,261 Points

I don't see any WHERE here. And I think aliases are OK in *ORDER BY*s and *HAVING*s.

It looks like you're working with the SQL Basics playground, but the reviews table there has a rating column instead of score. Also, are you sure you want an outer join, not a left join? And I think it's a bit easier to read if you GROUP BY something you are selecting (where possible). So perhaps you were going for something like this:

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

I tested this in the SQL Playground and confirmed that the alias worked in the HAVING clause.

If you were working on a specific course challenge, try posting from the "get help" function inside the challenge so it will link back to the question. Or just let me know if I was off track on my assumptions.

Sergey Podgornyy
Sergey Podgornyy
20,660 Points
Sergey Podgornyy
Sergey Podgornyy
20,660 Points

You can't refer to field aliases in the WHERE or HAVING clause. If you want to use the alias in your WHERE clause, you need to wrap it in a sub select, or CTE.

You can read more about on StackOverflow.

Steven Parker
Steven Parker
231,261 Points
Steven Parker
Steven Parker
231,261 Points

Perhaps it depends on the DB engine. The example I posted above works just fine here in the SQL Playground, even with my multi-word string literal alias. Try it yourself!