All right.
Moving on from the title an average rating challenge we now have something new for the first time we're
working with reviewers.
So what we have here is the first name of a reviewer the last name and then their rating.
So for every single rating that's been given we want to have the rating or for every review that's been
given out the rating.
And the first and last name of the reviewer who gave that rating.
So it's pretty similar to what we did up here with movies and ratings or series and ratings.
Except now we're working with the other tape.
So if we have these three tables right we've worked with let's go back here.
We've worked with series and reviews together.
Now we're working with reviewers and reviews so the outer two.
So let's get started on this one.
We will pop over here and just write challenge three.
Obviously the most important part of this entire video.
So we know we want to work with.
Let's start with reviewers.
That's the first thing you want to work with.
Let's take a look at those seven reviewers.
OK.
So for every review that these reviewers have written we want to see the corresponding rating.
And another side note I deliberately put in a reviewer who hasn't written any reviews.
We'll get to him or her in a little bit.
But we don't care about that one.
In this case.
So for this problem we don't need to worry about that.
OK.
So we've got to start from reviewers and then let's just do a select start from reviews.
And all we want to do is take these reviewer ideas.
Let's take three for Kimbra and find all the matches where reviewer ID is three so here's one here's
one here's one.
As you can see Cloud Nine is actually highlighting them for me which is really nice.
So we want to join on this right here so we want to join between reviewers and reviews where reviewer
ID is equal to reviews dot Ruut reviewer idea.
A lot of review saying that over and over is a bit of a tongue twister.
So it's like star from reviewers and we'll do a joyn reviews on.
And we want to do reviewer ID equals revues dot.
If she were ID.
It's a mouthful but hopefully it makes some sense you're starting to identify the patterns behind these
joints.
We've been doing this a couple of times now between the last section and this section.
So we're joining the two tables where reviewer ID is the same as a reviewer.
ID is the same as reviews review reviewer ID and what do you know that the same thing without the mess
that they just warned you about.
OK now I want to fix that.
We have a giant list for every review that's been made.
Over here we have the corresponding first last name and ID of the reviewer.
It is just more information than we needed to get because all we asked for was first name last name
and rating.
So let's now whittle it down.
So rather than star will select first name last name and rating just like that and now we get the right
result.
So I have a quick question which is what type of joint is this.
When we leave off the inner it's still implied.
So it's an inner join the intersection of those two circles the two tables.
And so because it's an inner join the Order doesn't actually matter.
So just to prove that to you first here's what we had before.
What you expect.
Now let's do the same thing but let's select from revues inner join reviewers.
OK.
So if we leave this at a star which is what we had before.
You can see that the order that our data is actually kind of printed in the basic sense when we use
start is different.
Right we have on the left the reviews and then the reviewers on the right.
But when we're hand picking things that we want like first name must have been reading.
It doesn't matter because we're dictating the order.
I'm saying I want first name then last name then rating.
So as long as the data is in the right place which both of these do they're combining them.
Whether we're doing a revues inner join reviewers or reviewers inner join reviews the same data is there
and then all we're doing is cherry picking what we want.
So both of these work just fine.