OK moving on to the next challenge with the same data.
Something a little new and frankly refreshing to see such a short table.
So what we're doing here is identifying the unreviewed Series in our database.
So there are a couple that nobody has written reviews for for whatever reason.
I haven't seen either of these shows really I've seen Malcolm in the middle.
Maybe once or twice as a kid but I don't feel like I can review it and apparently nobody else in our
app felt that way.
So we have to unreviewed shows.
And the point is not to cheat and to just you know use this information to find it but to actually find
it based off of the data we have in our database dynamically.
So how do we write.
It's going to be enjoying what type of joint Do we need to write where we can see what series have not
been reviewed.
OK well let's give it a shot.
We'll hop over to cloud nine and we'll start with our handy dandy comments.
We are on Challenge five and this one is unreviewed series.
OK so what tables are we going to need to join here.
So we've got if we go all the way back here we've got reviewers who got series and reviews and we don't
need to care about reviewers we don't care about names of who is reviewing things.
What we care about is overlap or lack of overlap between serious TV shows and reviews.
So let's start with a simple drawing select and we'll do star from series and we can just start with
that nice and simple doesn't tell us anything.
We have no idea which ones have been reviewed or not.
So we need to join it with the reviews.
That's going to be joyn reviews.
On and this is going to be our typical join So series that ID equals revues dot series ID and make this
explicitly enter.
Just so we're clear there.
And when we do this we get a lot right.
We see Archer with an idea of one that's been reviewed.
Eight point seven point five eight point five seven point seven but there's a problem here which is
it's not doing what we want.
We're not even we're not really close to what we want.
In fact we're ignoring the data we want because this is an inner join.
So this is only finding the exact intersection between reviews and series.
So wherever they overlap is what we're seeing but we also want to identify where they don't overlap
because that tells us that there's not a review for a series.
So what we want to use is a left join.
So hopefully that makes sense why.
Because on the left if we have our series right we have all the series some of which have matches some
of which don't.
And we do a left join.
It's going to take everything from the left including the ones that have no reviews and it's still going
to stick them in the join table.
But it's just going to fill their values with no.
So we'll be able to identify that.
So if we change it to left join
and we scroll through some of this you'll see somewhere.
I've got archery with all these reviews over here we keep going down here we go Mark in the middle.
Malcolm in the middle has NO NO NO.
Because there are no reviews to match it with but because we did a left join we still get Malcolm in
the middle and pushing dados which is also pushing data as he's pushing daisies which is no no no no
as well.
So we can use that to our advantage because wherever we see no means that there are no reviews.
So what we want to do first let's whittle it down a little bit.
What we're looking for at the end is just title but let's start by just doing title and rating.
Let's do that.
So it's a little easier to see.
OK.
So now what we want to do is only work with the data or there is no rating.
So that's actually quite simple.
We need to do is use a WHERE and then we can pick rating for example equals and we can say equals no.
Because that's problematic remember what we need to do is do is know.
Just like that.
So let's try this now and you'll see we only end up with these two title and rating and we don't have
to do rating.
By the way because there are other fields that are no.
If we go back to Star
there are quite a few things that are no.
We could have done Id although this is reviews that ID we could have done rading series ID or review
ID a reviewer ID but it doesn't matter which one we pick in this case.
So let's go back and all we actually want to do is select the title.
That's all we need here.
Pace set in OK.
Only thing that's missing is the alias.
So there's like title as unreviewed series.
Copy that in.
And it works OK.
So the key thing here was that we use a left join.
We wanted to highlight those discrepancies.
This is the this is a use case for a left join where you want to see where there isn't overlap.
So hopefully that that helps illustrate a bit more of the difference between inner and life joints because
if we had only done an inner join here we wouldn't get the result we want we wouldn't have any nulls.
So this wouldn't select anything at all.