OK we're at the final video here the final challenge and this is the one that puts everything together
are 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 right.
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 here of a series and then every rating that it
got along with the reviewer who wrote that reading.
So we've done the first part up until here right.
We've done title and writing together or where we've done reviewer and writing 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 cast 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 reviewers 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 serious ID we're going to get the name of the series instead of the reviewer.
We're going to get the name of the reviewer.
OK.
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.
OK.
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 reviewers 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 in with the reviews table.
So we'll have review hers along with their reviews along with the series associated with that review.
And we're not going to worry about you know people who haven't written reviews.
That's fine.
We're ignoring that.
OK.
So to do this let's start off first of all by joining it with
join reviews on reviewers ID because reviews it and then we need review were ID so fun to write and
we do that and we see things like OK Thomas Stoneman gave an 8.0 to some movie or some series idea for
one.
He also gave an eight point one to whatever has a D to 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 joint 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 the only thing that's left which is Series.
So we took reviewers and reviews said Sandridge them together where they intersect.
Now what we're going to do is join a series where the series A.D. is equal to series ID here inside
of the reviews.
So let's do inner join series on series start ID equals the reviews review Eric dot series underscore
ID such a nightmare trying to speak all this out loud.
But let's step through this.
OK so we have sort of this middle table let's say which is right here we have I.D. rating in 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 idea and find all matching series and stick them over here and
it's going to be a messy table.
It's gonna 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.
OK 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
at a point one and he gave Bob's Burgers at 7 Pernod and then we can see OK we have Archer again down
here from Wyatt Scaggs who gave it to seven point five.
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 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 a reviewer I think it's what we called it yep.
Okay.
And then there's only one thing that we need to change.
Other than that which is right now things are ordered by a reviewer.
Let's order them by title which is what we did over here.
So that's just a simple order by title.
Now what 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 are 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 reviews table where they match.
It's an inner join.
Or 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 match the idea of a series.
So it's a three way to joint statements but three tables together.
And that's why I decided to do many to many relationships after the simpler one too 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 for and move
on.
We're going to continue of course with joints and relationships but we're only building from here on
out.
So I can grab it and making it through this part.