All right.
Moving on from the title and 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, we want the rating and then 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 table.
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 we want to work with.
Let's take a look at those seven reviewers.
Okay.
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.
Okay.
So we've got select star from reviewers and then let's just do a select star from reviews.
And all we want to do is take these reviewer IDs.
Let's take three for Kimbra and find all the matches where reviewer ID is three.
So 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 Reviewer
ID, it's a lot of review saying that over and over is a bit of a tongue twister.
So select star from reviewers and we'll do a join reviews on and we want to do reviewer ID equals reviews
dot reviewer 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 these a couple of times now between the last section in this section.
So we're joining the two tables where reviewer ID is the same as a reviewer, dot ID is the same as
reviews dot reviewer ID and what do you know did the same thing without the SE that I just warned you
about?
Okay, so now once we 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.
This is more information than we needed to get because all we asked for was first name, last name and
rating.
So let's now whittle that down.
So rather than Star, we'll 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 you 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 reviews and inner join review worse.
So if we leave this at 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 Star is different, right?
We have on the left the reviews and then the reviewers on the right.
But when we're handpicking things that we want, like first name, last name and rating, 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 reviews, inner join reviewers or reviewers in our 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.