WEBVTT 00:00.440 --> 00:00.890 All right. 00:00.900 --> 00:06.290 Moving on from the title an average rating challenge we now have something new for the first time we're 00:06.290 --> 00:08.060 working with reviewers. 00:08.400 --> 00:14.860 So what we have here is the first name of a reviewer the last name and then their rating. 00:14.910 --> 00:20.550 So for every single rating that's been given we want to have the rating or for every review that's been 00:20.550 --> 00:21.800 given out the rating. 00:21.930 --> 00:25.820 And the first and last name of the reviewer who gave that rating. 00:26.400 --> 00:32.390 So it's pretty similar to what we did up here with movies and ratings or series and ratings. 00:32.610 --> 00:35.000 Except now we're working with the other tape. 00:35.100 --> 00:40.610 So if we have these three tables right we've worked with let's go back here. 00:40.800 --> 00:45.050 We've worked with series and reviews together. 00:45.150 --> 00:50.330 Now we're working with reviewers and reviews so the outer two. 00:50.370 --> 00:53.320 So let's get started on this one. 00:53.460 --> 00:58.130 We will pop over here and just write challenge three. 00:58.380 --> 01:01.760 Obviously the most important part of this entire video. 01:02.220 --> 01:05.040 So we know we want to work with. 01:05.070 --> 01:06.910 Let's start with reviewers. 01:07.110 --> 01:10.310 That's the first thing you want to work with. 01:10.350 --> 01:12.500 Let's take a look at those seven reviewers. 01:12.900 --> 01:13.380 OK. 01:13.560 --> 01:19.570 So for every review that these reviewers have written we want to see the corresponding rating. 01:19.920 --> 01:25.230 And another side note I deliberately put in a reviewer who hasn't written any reviews. 01:25.230 --> 01:27.380 We'll get to him or her in a little bit. 01:27.630 --> 01:29.190 But we don't care about that one. 01:29.220 --> 01:30.000 In this case. 01:30.210 --> 01:33.520 So for this problem we don't need to worry about that. 01:33.570 --> 01:33.980 OK. 01:34.200 --> 01:39.440 So we've got to start from reviewers and then let's just do a select start from reviews. 01:42.390 --> 01:45.630 And all we want to do is take these reviewer ideas. 01:45.630 --> 01:52.710 Let's take three for Kimbra and find all the matches where reviewer ID is three so here's one here's 01:52.710 --> 01:54.440 one here's one. 01:54.840 --> 01:59.720 As you can see Cloud Nine is actually highlighting them for me which is really nice. 02:00.120 --> 02:10.010 So we want to join on this right here so we want to join between reviewers and reviews where reviewer 02:10.260 --> 02:13.930 ID is equal to reviews dot Ruut reviewer idea. 02:14.010 --> 02:18.920 A lot of review saying that over and over is a bit of a tongue twister. 02:19.020 --> 02:28.560 So it's like star from reviewers and we'll do a joyn reviews on. 02:28.560 --> 02:34.740 And we want to do reviewer ID equals revues dot. 02:34.770 --> 02:36.250 If she were ID. 02:36.630 --> 02:41.940 It's a mouthful but hopefully it makes some sense you're starting to identify the patterns behind these 02:41.940 --> 02:43.270 joints. 02:43.380 --> 02:47.370 We've been doing this a couple of times now between the last section and this section. 02:47.370 --> 02:52.930 So we're joining the two tables where reviewer ID is the same as a reviewer. 02:53.040 --> 03:01.000 ID is the same as reviews review reviewer ID and what do you know that the same thing without the mess 03:01.010 --> 03:02.640 that they just warned you about. 03:03.130 --> 03:06.630 OK now I want to fix that. 03:06.630 --> 03:10.460 We have a giant list for every review that's been made. 03:11.440 --> 03:17.030 Over here we have the corresponding first last name and ID of the reviewer. 03:17.260 --> 03:22.180 It is just more information than we needed to get because all we asked for was first name last name 03:22.180 --> 03:23.140 and rating. 03:23.140 --> 03:24.800 So let's now whittle it down. 03:24.870 --> 03:38.460 So rather than star will select first name last name and rating just like that and now we get the right 03:38.460 --> 03:39.290 result. 03:39.660 --> 03:45.360 So I have a quick question which is what type of joint is this. 03:45.510 --> 03:47.690 When we leave off the inner it's still implied. 03:47.820 --> 03:51.800 So it's an inner join the intersection of those two circles the two tables. 03:52.380 --> 03:56.360 And so because it's an inner join the Order doesn't actually matter. 03:56.520 --> 04:01.260 So just to prove that to you first here's what we had before. 04:01.260 --> 04:03.170 What you expect. 04:03.330 --> 04:11.540 Now let's do the same thing but let's select from revues inner join reviewers. 04:12.220 --> 04:12.990 OK. 04:13.650 --> 04:17.830 So if we leave this at a star which is what we had before. 04:18.990 --> 04:23.700 You can see that the order that our data is actually kind of printed in the basic sense when we use 04:23.740 --> 04:25.390 start is different. 04:25.390 --> 04:31.140 Right we have on the left the reviews and then the reviewers on the right. 04:31.740 --> 04:36.310 But when we're hand picking things that we want like first name must have been reading. 04:36.390 --> 04:38.970 It doesn't matter because we're dictating the order. 04:38.970 --> 04:41.870 I'm saying I want first name then last name then rating. 04:42.270 --> 04:47.610 So as long as the data is in the right place which both of these do they're combining them. 04:47.610 --> 04:53.850 Whether we're doing a revues inner join reviewers or reviewers inner join reviews the same data is there 04:54.630 --> 04:56.980 and then all we're doing is cherry picking what we want. 04:57.330 --> 04:59.280 So both of these work just fine.