Okay.
We're at the final video here, the final challenge and this is the one that puts everything together
or really more what it's doing is putting together all three tables that we have.
So what we're actually getting out of it is not that impressive.
We're not doing any crazy analytics or anything like the previous one.
Not that that was too crazy, but nothing like that.
All that we're doing is putting the title of a movie or a series and then every rating that it got along
with the reviewer who wrote that rating.
So we've done the first part up until here, right where we've done title and rating together or where
we've done reviewer and rating together.
But now we're going to put them all together so that this is the information for one review, right?
It's the movie that was the series that was reviewed.
It was the person who reviewed it.
Notice that we can cut them into one reviewer instead of first name and last name, but that's secondary
at the end.
So we have the series title, the reviewer's name, and then the rating they gave it.
So we're basically just trying to take let's do it here.
Take this.
And fill in the blanks.
So instead of series ID, we're going to get the name of the series.
Instead of the reviewer ID, we're going to get the name of the reviewer.
So let's go ahead and start over here with our handy dandy comment.
And we are on what is this seven?
Six.
Yes, seven.
Three tables.
So we can start off with any of the tables.
We're going to join them all together.
But I'll start with reviewers.
So we'll select start from your viewers just like that.
Except I need from boring.
We've done this before.
Blah, blah, blah, blah, blah.
So the first step is to join it with the reviews table.
So we'll have review ers along with their reviews, along with the series associated with that review.
And we're not going to worry about people who haven't written reviews.
That's fine.
We're ignoring that.
So to do this, let's start off, first of all, by joining it with.
Join reviews.
On reviewers dot id equals reviews dot.
And then we need reviewer ID.
So fun to write.
And we do that and we see things like OC.
Thomas Stoneman gave an 8.0 to some movie.
There are some series with ID of one.
He also gave an 8.1 to whatever has ad2 and so on.
So we're getting close ish.
Now what we need to do is also throw on the series data over here, and the first thing I'll do is just
make this explicit.
It's an inner join, so we're going to do another join.
And it's really easy, at least syntactically, it's easy.
We just write inner join again.
So what are we joining on this time?
Well, we're now going to join on only thing that's left, which is series.
So we took reviewers and reviews Sandridge them together where they intersect.
Now what we're going to do is join a series where the series ID is equal to series ID here inside of
the reviews.
So let's do inner join series.
On Series ID equals reviews dot review dot series underscore ID such a nightmare trying to speak all
this out loud, but let's step through this.
So we have sort of this middle table, let's say, which is right here.
We have ID rating and series ID and reviewer ID for our review that every review has that.
And what we started off was joining it with the relevant reviewers.
Now we're going to also take the series ID and find all matching series and stick them over here.
And it's going to be a messy table.
It's going to get all collapsed and screwed up.
So let's filter out some things.
So let's only work with the title, the rating, first name and last name.
Okay.
So we're getting close now, so we're pretty much there.
We have all of the data together.
So we have Archer has an 8.0 rating from Thomas Stoneman.
Thomas Stoneman also gave Arrested Development an 8.1, and he gave Bob's Burgers at 7.0.
And then we can see we have Archer again down here from Wyatt Scaggs, who gave it a 7.5.
So we have all of our data synthesized in one table.
Now, what we need to do is concatenate things together over here.
So that's a simple all we need to do is instead of doing first name and last name, we'll do a king
cat.
First name, comma.
Space.
Last name as reviewer, I think, is what we called it.
Yep.
And then there's only one thing that we need to change other than that, which is right now things are
ordered by reviewer.
Let's order them by title, which is what we did over here.
So that's just a simple order by title.
Now we should be done.
If I get rid of that extra semicolon.
And here we go.
So we have Archer, all of the ratings for Archer and the people who wrote them.
Arrested Development, all the ratings and people who wrote them and all the way down through all of
our series that have reviews.
So that's important.
It's only where we have reviews.
So to summarize, let's go all the way back here.
What we just did was basically take the reviewers table, smash it with the reviews table where they
match.
It's an inner join.
Where's the overlap?
Right.
So we take the overlap between them based off of ID here and reviewer ID.
Then we have that second table that we then joined with series where the series ID matched the ID of
a series.
So it's a three way to join statements but three tables together.
And that's why I decided to do many to many relationships after the simpler one to many.
But hopefully it's not too intimidating.
We did a bunch of exercises with them.
You're probably a bit burnt out.
I know I am from recording all of them, so I definitely recommend that you take a break.
Before we move on, we're going to continue, of course, with joint and relationships, but we're only
building from here on out.
So congrats on making it through this part.