1 00:00:00,450 --> 00:00:00,920 All right. 2 00:00:00,930 --> 00:00:05,370 Moving on from the title and average rating challenge, we now have something new. 3 00:00:05,400 --> 00:00:07,980 For the first time, we're working with reviewers. 4 00:00:08,280 --> 00:00:14,850 So what we have here is the first name of a reviewer, the last name and then their rating. 5 00:00:14,850 --> 00:00:20,460 So for every single rating that's been given, we want to have the rating or for every review that's 6 00:00:20,460 --> 00:00:25,620 been given, we want the rating and then the first and last name of the reviewer who gave that rating. 7 00:00:26,250 --> 00:00:32,940 So it's pretty similar to what we did up here with movies and ratings or series and ratings, except 8 00:00:32,940 --> 00:00:34,980 now we're working with the other table. 9 00:00:34,980 --> 00:00:40,080 So if we have these three tables, right, we've worked with Let's go back here. 10 00:00:40,650 --> 00:00:44,640 We've worked with series and reviews together. 11 00:00:44,970 --> 00:00:48,360 Now we're working with reviewers and reviews. 12 00:00:48,780 --> 00:00:50,280 So the outer two. 13 00:00:50,280 --> 00:00:51,900 So let's get started on this one. 14 00:00:53,370 --> 00:01:00,660 We will pop over here and just write Challenge three, obviously the most important part of this entire 15 00:01:00,990 --> 00:01:06,960 video so we know we want to work with let's start with reviewers. 16 00:01:06,960 --> 00:01:09,330 That's the first thing we want to work with. 17 00:01:10,350 --> 00:01:12,390 Let's take a look at those seven reviewers. 18 00:01:12,900 --> 00:01:13,250 Okay. 19 00:01:13,440 --> 00:01:19,410 So for every review that these reviewers have written, we want to see the corresponding rating. 20 00:01:19,740 --> 00:01:25,230 And another side note, I deliberately put in a reviewer who hasn't written any reviews. 21 00:01:25,230 --> 00:01:30,030 We'll get to him or her in a little bit, but we don't care about that one in this case. 22 00:01:30,030 --> 00:01:32,640 So for this problem, we don't need to worry about that. 23 00:01:33,570 --> 00:01:33,920 Okay. 24 00:01:34,080 --> 00:01:38,730 So we've got select star from reviewers and then let's just do a select star from reviews. 25 00:01:42,320 --> 00:01:45,640 And all we want to do is take these reviewer IDs. 26 00:01:45,650 --> 00:01:51,200 Let's take three for Kimbra and find all the matches where reviewer ID is three. 27 00:01:51,200 --> 00:01:52,100 So here's one. 28 00:01:52,340 --> 00:01:53,300 Here's one. 29 00:01:53,420 --> 00:01:54,410 Here's one. 30 00:01:54,860 --> 00:01:59,540 As you can see, cloud nine is actually highlighting them for me, which is really nice. 31 00:01:59,960 --> 00:02:03,950 So we want to join on this right here. 32 00:02:03,950 --> 00:02:13,550 So we want to join between reviewers and reviews where reviewer ID is equal to reviews DOT Reviewer 33 00:02:13,550 --> 00:02:18,710 ID, it's a lot of review saying that over and over is a bit of a tongue twister. 34 00:02:18,860 --> 00:02:33,320 So select star from reviewers and we'll do a join reviews on and we want to do reviewer ID equals reviews 35 00:02:33,320 --> 00:02:39,440 dot reviewer ID it's a mouthful, but hopefully it makes some sense. 36 00:02:39,440 --> 00:02:42,680 You're starting to identify the patterns behind these joints. 37 00:02:43,220 --> 00:02:47,210 We've been doing these a couple of times now between the last section in this section. 38 00:02:47,210 --> 00:02:55,010 So we're joining the two tables where reviewer ID is the same as a reviewer, dot ID is the same as 39 00:02:55,010 --> 00:03:01,790 reviews dot reviewer ID and what do you know did the same thing without the SE that I just warned you 40 00:03:01,790 --> 00:03:02,330 about? 41 00:03:02,990 --> 00:03:10,490 Okay, so now once we fix that, we have a giant list for every review that's been made. 42 00:03:11,410 --> 00:03:16,720 Over here, we have the corresponding first last name and ID of the reviewer. 43 00:03:17,300 --> 00:03:22,330 This is more information than we needed to get because all we asked for was first name, last name and 44 00:03:22,330 --> 00:03:22,900 rating. 45 00:03:22,960 --> 00:03:24,820 So let's now whittle that down. 46 00:03:24,940 --> 00:03:33,130 So rather than Star, we'll select first name, last name and rating. 47 00:03:33,550 --> 00:03:34,570 Just like that. 48 00:03:36,870 --> 00:03:39,180 And now we get the right result. 49 00:03:39,510 --> 00:03:43,110 So I have a quick question, which is what type of joint is this? 50 00:03:45,490 --> 00:03:50,110 When you leave off the inner it's still implied so it's an inner join the intersection of those two 51 00:03:50,110 --> 00:03:56,370 circles the two tables and so because it's an inner join the order doesn't actually matter. 52 00:03:56,380 --> 00:04:02,200 So just to prove that to you first, here's what we had before, what you expect. 53 00:04:03,220 --> 00:04:11,140 Now let's do the same thing, but let's select from reviews and inner join review worse. 54 00:04:13,570 --> 00:04:20,680 So if we leave this at Star, which is what we had before, you can see that the order that our data 55 00:04:20,680 --> 00:04:25,600 is actually kind of printed in the basic sense when we use Star is different, right? 56 00:04:25,600 --> 00:04:30,160 We have on the left the reviews and then the reviewers on the right. 57 00:04:31,630 --> 00:04:36,760 But when we're handpicking things that we want, like first name, last name and rating, it doesn't 58 00:04:36,760 --> 00:04:38,860 matter because we're dictating the order. 59 00:04:38,860 --> 00:04:41,680 I'm saying I want first name, then last name, then rating. 60 00:04:42,160 --> 00:04:47,440 So as long as the data is in the right place, which both of these do, they're combining them. 61 00:04:47,440 --> 00:04:53,410 Whether we're doing reviews, inner join reviewers or reviewers in our join reviews, the same data 62 00:04:53,410 --> 00:04:54,100 is there. 63 00:04:54,460 --> 00:04:56,890 And then all we're doing is cherry picking what we want. 64 00:04:57,190 --> 00:04:59,290 So both of these work just fine.