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

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

michaelmugo
michaelmugo
3,971 Points
Posted by michaelmugo
michaelmugo
3,971 Points

I don't understand the combination of JOIN and GROUP BY

So I understand how GROUP BY works to combine result-sets by columns, and it makes sense coming from the same table: reviews.

SELECT movie_id, MIN(score) AS minimum_score, MAX(score) AS maximum_score,
AVG(score) AS average, 
FROM reviews GROUP BY movie_id;

But later on in the video, we SELECT title to show the results in a more human-understandable form:

SELECT title, MIN(score) AS minimum_score, MAX(score) AS maximum_score,
AVG(score) AS average, 
FROM movies JOIN reviews ON movies.id = reviews.movie_id 
GROUP BY movie_id;

How exactly are the numeric functions still able to get their data, isn't the movies table being used where there's no score ?

Maybe I'm not understanding it properly, but as far as I've learnt the numeric functions use the data using the FROM some_given_table

1 Answer

Kevin Korte
Kevin Korte
28,149 Points
Kevin Korte
Kevin Korte
28,149 Points

I'm not sure I can explain it well, but the movies table is getting the reviews because that infor is being joined to the movie table matching the id column on the movie table with the movie_id column on the reviews table.

In many MVC frameworks, this would be handled by a has_many and belongs_to relationship. A movie has_many reviews, and a review belongs_to a movie.

I reference this from time to time, hopefully it helps you. http://www.sitepoint.com/understanding-sql-joins-mysql-database/

michaelmugo
michaelmugo
3,971 Points
michaelmugo
michaelmugo
3,971 Points

Thanks! You're answer actually makes sense! So the numeric functions have access to the entire result-set of the SELECT query, even those in reviews by JOIN?

Thx for the reference

Elena Paraschiv
Elena Paraschiv
9,938 Points
Elena Paraschiv
Elena Paraschiv
9,938 Points

If we forget that id is a primary key or movie_id is a foreign key can we search in the database for this?