All right.
It's time for our final challenge of this section.
And this one is easy in one sense.
I'm not asking you to do any math aggregation.
There's no group involved.
All that you're doing is taking each review and filling in its title that it was review of review for
the rating and who left the review.
And this should be a full name so you can cat that.
So there's no grouping or anything.
But what makes it hard is that it is it involves something we have not done yet, which is to joins
because we're going to take this middle table.
Right.
If we go back to our diagram.
This review's table.
We're going to keep the rating, but then we're going to go and get the reviewers first and last name
from the reviewer ID based off of that Foreign key and the title from the series based off of the series
ID, so you'll have to do two joints.
Now the joint syntax is the same.
You'll just do one after another, so try it on your own if you'd like.
Otherwise, we'll do it together.
Here we go.
All right, so let's do the first half.
Let's get title next to rating.
We've done that before.
So we'll do something like select title comma rating from.
And we can start from.
It doesn't really matter but let's say from reviews join series on where the reviews series ID equals
the series ID.
So that should give us every title and every rating or every title that is associated with a given rating.
Then let's instead display everything.
Start.
Because you'll see, of course, we have a reviewer ID for every review.
So we're going to take the reviewer ID and join with the reviewers table based off of that ID.
And it's as simple as just repeating the join keyword and I'll be explicit and say inner join.
You don't have to.
We're going to join on.
Well, first join the reviewers table on where the reviews dot reviewer ID, which we've done this before.
It's the same query, the same join we did here is equal to reviewer dot ID or reviewers ID.
So if I do this.
Hoof.
There's a lot to look at.
If I zoom way out.
What we should see is for every review that was left, we have all the information from the series title,
release your genre and all the information of the reviewer.
But we just want first name and last name and title and rating.
Right.
We want to make this.
So let's refine it a bit more.
Select title rating and then can cat first name and last name and that should do it.
Well, if we label this as reviewer or something like that, run this query.
Oh, I should have concatenated a space, but otherwise we're pretty much there.
So let's add that space in.
OC.
And now we have every review filled in with the title that the review belongs to or is associated with
and the reviewer who left the review.
And so we did that by filling in based upon the series ID and the reviewer ID with two joints.
Now, again, I said the order doesn't really matter.
We could rewrite this to start with series and then join with reviews on reviews that series ID, equal
series that ID and then enjoin reviewers.
And this still should work fine.
Same end result.
We could also go the other way around and start with selecting from reviewers in or join on reviews.
And we're going to do reviews dot.
Reviewer's ID equals reviewer ID.
Man, I'm getting tired of saying review and reviewer, and then we'll enter.
Join that on series where the reviews dot series ID equals series dot ID.
So as long as we're joining the same the tables together.
Oh, I have a mistake here.
Reviewer ID there we are.
And I need an SX here as well.
Getting sloppy there.
But what I was trying to say before I kept making errors is that we as long as we join the same three
tables on the same logic, right.
Using the same reviewer ID equals reviewers, date ID series ID equals series that ID the order doesn't
matter now.
The order would matter if we were going to do like a left join versus a right join.
It doesn't matter in this situation at all.
And now we are done with this exercise.
So next we're going to get more practice working with relationships.
Many to many one to many join tables, writing, joins in or join left and all that fun stuff with a
totally different little project, if you want to call it a project, a different data set.
So that's coming up.