WEBVTT 00:00.270 --> 00:03.290 OK moving on to the next challenge with the same data. 00:03.450 --> 00:08.180 Something a little new and frankly refreshing to see such a short table. 00:08.190 --> 00:12.760 So what we're doing here is identifying the unreviewed Series in our database. 00:12.780 --> 00:17.560 So there are a couple that nobody has written reviews for for whatever reason. 00:17.760 --> 00:20.780 I haven't seen either of these shows really I've seen Malcolm in the middle. 00:20.820 --> 00:26.180 Maybe once or twice as a kid but I don't feel like I can review it and apparently nobody else in our 00:26.180 --> 00:27.110 app felt that way. 00:27.210 --> 00:29.800 So we have to unreviewed shows. 00:29.940 --> 00:35.820 And the point is not to cheat and to just you know use this information to find it but to actually find 00:35.820 --> 00:39.030 it based off of the data we have in our database dynamically. 00:39.120 --> 00:39.960 So how do we write. 00:39.960 --> 00:45.240 It's going to be enjoying what type of joint Do we need to write where we can see what series have not 00:45.240 --> 00:46.410 been reviewed. 00:47.040 --> 00:48.560 OK well let's give it a shot. 00:48.600 --> 00:54.480 We'll hop over to cloud nine and we'll start with our handy dandy comments. 00:54.540 --> 01:01.370 We are on Challenge five and this one is unreviewed series. 01:01.380 --> 01:05.750 OK so what tables are we going to need to join here. 01:06.070 --> 01:15.430 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 01:15.430 --> 01:20.320 need to care about reviewers we don't care about names of who is reviewing things. 01:20.380 --> 01:28.460 What we care about is overlap or lack of overlap between serious TV shows and reviews. 01:28.600 --> 01:40.210 So let's start with a simple drawing select and we'll do star from series and we can just start with 01:40.750 --> 01:44.120 that nice and simple doesn't tell us anything. 01:44.230 --> 01:46.460 We have no idea which ones have been reviewed or not. 01:46.490 --> 01:48.570 So we need to join it with the reviews. 01:48.680 --> 01:52.400 That's going to be joyn reviews. 01:53.540 --> 02:09.710 On and this is going to be our typical join So series that ID equals revues dot series ID and make this 02:09.980 --> 02:11.070 explicitly enter. 02:11.080 --> 02:12.780 Just so we're clear there. 02:13.470 --> 02:17.520 And when we do this we get a lot right. 02:17.660 --> 02:22.660 We see Archer with an idea of one that's been reviewed. 02:22.700 --> 02:27.630 Eight point seven point five eight point five seven point seven but there's a problem here which is 02:27.630 --> 02:28.690 it's not doing what we want. 02:28.700 --> 02:32.780 We're not even we're not really close to what we want. 02:32.780 --> 02:37.030 In fact we're ignoring the data we want because this is an inner join. 02:37.550 --> 02:43.620 So this is only finding the exact intersection between reviews and series. 02:44.060 --> 02:50.270 So wherever they overlap is what we're seeing but we also want to identify where they don't overlap 02:50.300 --> 02:54.730 because that tells us that there's not a review for a series. 02:54.770 --> 02:57.450 So what we want to use is a left join. 02:57.980 --> 02:59.770 So hopefully that makes sense why. 02:59.780 --> 03:05.960 Because on the left if we have our series right we have all the series some of which have matches some 03:05.960 --> 03:06.840 of which don't. 03:06.920 --> 03:08.620 And we do a left join. 03:08.990 --> 03:14.270 It's going to take everything from the left including the ones that have no reviews and it's still going 03:14.270 --> 03:15.470 to stick them in the join table. 03:15.590 --> 03:17.930 But it's just going to fill their values with no. 03:18.050 --> 03:19.880 So we'll be able to identify that. 03:19.910 --> 03:21.380 So if we change it to left join 03:24.580 --> 03:29.310 and we scroll through some of this you'll see somewhere. 03:29.350 --> 03:37.460 I've got archery with all these reviews over here we keep going down here we go Mark in the middle. 03:37.470 --> 03:40.740 Malcolm in the middle has NO NO NO. 03:40.890 --> 03:45.720 Because there are no reviews to match it with but because we did a left join we still get Malcolm in 03:45.720 --> 03:52.430 the middle and pushing dados which is also pushing data as he's pushing daisies which is no no no no 03:52.470 --> 03:53.200 as well. 03:53.370 --> 04:00.240 So we can use that to our advantage because wherever we see no means that there are no reviews. 04:00.780 --> 04:04.720 So what we want to do first let's whittle it down a little bit. 04:04.800 --> 04:13.640 What we're looking for at the end is just title but let's start by just doing title and rating. 04:13.800 --> 04:16.390 Let's do that. 04:16.400 --> 04:18.090 So it's a little easier to see. 04:18.490 --> 04:19.390 OK. 04:19.790 --> 04:25.070 So now what we want to do is only work with the data or there is no rating. 04:25.700 --> 04:27.810 So that's actually quite simple. 04:27.920 --> 04:35.930 We need to do is use a WHERE and then we can pick rating for example equals and we can say equals no. 04:36.380 --> 04:41.380 Because that's problematic remember what we need to do is do is know. 04:41.480 --> 04:43.340 Just like that. 04:43.340 --> 04:50.690 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 04:50.690 --> 04:51.320 to do rating. 04:51.320 --> 04:54.330 By the way because there are other fields that are no. 04:54.620 --> 04:55.880 If we go back to Star 04:58.820 --> 05:00.980 there are quite a few things that are no. 05:01.730 --> 05:10.520 We could have done Id although this is reviews that ID we could have done rading series ID or review 05:10.520 --> 05:16.220 ID a reviewer ID but it doesn't matter which one we pick in this case. 05:16.310 --> 05:19.440 So let's go back and all we actually want to do is select the title. 05:19.580 --> 05:20.740 That's all we need here. 05:22.390 --> 05:24.490 Pace set in OK. 05:24.720 --> 05:26.910 Only thing that's missing is the alias. 05:26.910 --> 05:30.810 So there's like title as unreviewed series. 05:30.810 --> 05:31.790 Copy that in. 05:32.250 --> 05:34.030 And it works OK. 05:34.170 --> 05:36.990 So the key thing here was that we use a left join. 05:37.230 --> 05:39.380 We wanted to highlight those discrepancies. 05:39.540 --> 05:45.780 This is the this is a use case for a left join where you want to see where there isn't overlap. 05:45.810 --> 05:50.550 So hopefully that that helps illustrate a bit more of the difference between inner and life joints because 05:50.550 --> 05:56.420 if we had only done an inner join here we wouldn't get the result we want we wouldn't have any nulls. 05:56.490 --> 05:58.750 So this wouldn't select anything at all.