1 00:00:00,150 --> 00:00:01,589 Next up, we have a third challenge. 2 00:00:01,589 --> 00:00:06,090 This time we're working with the reviewers table and the reviews table. 3 00:00:06,360 --> 00:00:13,140 So what I want us to do is match up every reviewer's first and last name with any review they have made. 4 00:00:13,320 --> 00:00:14,520 We'll see the rating. 5 00:00:14,850 --> 00:00:21,180 So Thomas Stoneman left an 8.4 something we don't know what yet and an eight one for something else. 6 00:00:21,180 --> 00:00:22,230 A seven for something else. 7 00:00:22,230 --> 00:00:24,660 7595 for something I want. 8 00:00:24,660 --> 00:00:30,330 First name and last name of every reviewer matched up with any rating they have left which would be 9 00:00:30,330 --> 00:00:32,100 stored in the reviews table. 10 00:00:32,910 --> 00:00:34,320 So let's go ahead and try it. 11 00:00:34,320 --> 00:00:40,020 We're going to need a join we're going to join the reviewers with reviews, those two tables. 12 00:00:40,020 --> 00:00:42,960 We won't worry about series because there's no series information here. 13 00:00:43,750 --> 00:00:44,380 Okay. 14 00:00:45,580 --> 00:00:55,030 We'll start with a select first name and the last name from reviewers, of course. 15 00:00:55,030 --> 00:00:57,100 And I'll put the ID in there as well. 16 00:00:57,250 --> 00:01:04,599 Just to start, I run this and we see our seven reviewers Thomas Stoneman, Wyatt Skaggs, Kimbra Masters, 17 00:01:04,599 --> 00:01:06,940 one down to seven four their IDs. 18 00:01:06,940 --> 00:01:16,210 And we are going to match up those IDs from the reviewers table with the reviewer's ID from the reviews 19 00:01:16,210 --> 00:01:16,720 table. 20 00:01:16,720 --> 00:01:20,260 It's kind of confusing having reviewers and review. 21 00:01:21,270 --> 00:01:25,110 Maybe users and reviews would have been simpler or less confusing. 22 00:01:25,110 --> 00:01:27,630 But either way, we have these two tables. 23 00:01:27,930 --> 00:01:36,060 I want to match up this idea of one with this review that has a reviewer ID of one and so does this 24 00:01:36,060 --> 00:01:37,720 one, and so does this one. 25 00:01:37,740 --> 00:01:42,030 And then I want to take the rating from each of those once I join them together. 26 00:01:42,600 --> 00:01:43,950 So I'm going to do a join. 27 00:01:44,670 --> 00:01:47,280 I'll take ID first name, last name from reviewers. 28 00:01:47,730 --> 00:01:56,040 Join reviews on where the reviews dot reviewer. 29 00:01:57,870 --> 00:02:02,250 ID is equal to the reviewer to her ID. 30 00:02:02,610 --> 00:02:08,820 If you struggle to understand this, just pause and walk through what is this in an actual row of a 31 00:02:08,820 --> 00:02:09,449 table? 32 00:02:09,449 --> 00:02:10,650 And what is this? 33 00:02:10,650 --> 00:02:11,670 And compare them. 34 00:02:11,670 --> 00:02:12,930 It's a lot of review. 35 00:02:12,930 --> 00:02:14,370 Review or reviewer. 36 00:02:14,400 --> 00:02:21,960 It's confusing, but we're just matching up where the ID from the reviewer matches with the reviewer's 37 00:02:21,960 --> 00:02:24,090 ID in the review table. 38 00:02:24,090 --> 00:02:26,100 Oh, what a nightmare to say that out loud. 39 00:02:26,100 --> 00:02:31,500 And instead of just doing ID, first name and last name, let's do Star to begin with. 40 00:02:33,570 --> 00:02:38,190 And of course it's reviewer's ID. 41 00:02:38,550 --> 00:02:40,200 I hope you saw that before me. 42 00:02:40,230 --> 00:02:40,720 Okay. 43 00:02:41,130 --> 00:02:52,110 Now we see every time there is a match between the reviewer ID and the reviews reviewer ID one and one 44 00:02:52,110 --> 00:02:56,100 or two and two, six and six. 45 00:02:56,100 --> 00:02:58,560 We see it all alongside and that's great. 46 00:02:58,560 --> 00:02:59,160 That's what we want. 47 00:02:59,160 --> 00:03:02,070 Our two tables joined on that intersection. 48 00:03:02,070 --> 00:03:07,650 The user or reviewers ID matching what we stored in the actual reviews table. 49 00:03:08,040 --> 00:03:10,530 But all we want is first name, last name and rating. 50 00:03:10,530 --> 00:03:11,940 So let's slim that down. 51 00:03:13,500 --> 00:03:16,710 First name, last name and rating. 52 00:03:17,440 --> 00:03:19,690 Try it again, and this should get us there. 53 00:03:20,440 --> 00:03:21,280 Looks good. 54 00:03:21,310 --> 00:03:26,590 All of Thomas Donovan's ratings, all of Wyatt Skaggs ratings can remasters. 55 00:03:26,590 --> 00:03:28,420 And that's what we were supposed to do. 56 00:03:28,960 --> 00:03:33,910 Of course, I limited it to as many could fit in the screenshot, but I want all of them and we are 57 00:03:33,910 --> 00:03:34,870 getting all of them here. 58 00:03:35,230 --> 00:03:35,940 All right. 59 00:03:35,950 --> 00:03:38,170 Next up, yet another challenge.