1 00:00:00,090 --> 00:00:00,510 All right. 2 00:00:00,510 --> 00:00:03,270 It's time for our final challenge of this section. 3 00:00:03,510 --> 00:00:06,720 And this one is easy in one sense. 4 00:00:06,720 --> 00:00:10,470 I'm not asking you to do any math aggregation. 5 00:00:10,470 --> 00:00:12,060 There's no group involved. 6 00:00:12,060 --> 00:00:19,440 All that you're doing is taking each review and filling in its title that it was review of review for 7 00:00:19,470 --> 00:00:21,960 the rating and who left the review. 8 00:00:21,960 --> 00:00:24,450 And this should be a full name so you can cat that. 9 00:00:24,450 --> 00:00:26,040 So there's no grouping or anything. 10 00:00:26,040 --> 00:00:33,090 But what makes it hard is that it is it involves something we have not done yet, which is to joins 11 00:00:33,360 --> 00:00:35,670 because we're going to take this middle table. 12 00:00:35,710 --> 00:00:35,910 Right. 13 00:00:35,910 --> 00:00:37,440 If we go back to our diagram. 14 00:00:38,510 --> 00:00:39,850 This review's table. 15 00:00:39,860 --> 00:00:45,170 We're going to keep the rating, but then we're going to go and get the reviewers first and last name 16 00:00:45,170 --> 00:00:51,920 from the reviewer ID based off of that Foreign key and the title from the series based off of the series 17 00:00:51,920 --> 00:00:54,800 ID, so you'll have to do two joints. 18 00:00:54,950 --> 00:00:57,110 Now the joint syntax is the same. 19 00:00:57,110 --> 00:01:00,080 You'll just do one after another, so try it on your own if you'd like. 20 00:01:00,110 --> 00:01:01,700 Otherwise, we'll do it together. 21 00:01:01,700 --> 00:01:02,960 Here we go. 22 00:01:02,990 --> 00:01:05,570 All right, so let's do the first half. 23 00:01:05,570 --> 00:01:07,490 Let's get title next to rating. 24 00:01:07,490 --> 00:01:08,600 We've done that before. 25 00:01:09,650 --> 00:01:15,290 So we'll do something like select title comma rating from. 26 00:01:15,380 --> 00:01:17,030 And we can start from. 27 00:01:17,180 --> 00:01:29,750 It doesn't really matter but let's say from reviews join series on where the reviews series ID equals 28 00:01:29,750 --> 00:01:31,670 the series ID. 29 00:01:31,880 --> 00:01:38,330 So that should give us every title and every rating or every title that is associated with a given rating. 30 00:01:39,310 --> 00:01:42,250 Then let's instead display everything. 31 00:01:43,090 --> 00:01:43,780 Start. 32 00:01:45,450 --> 00:01:51,210 Because you'll see, of course, we have a reviewer ID for every review. 33 00:01:51,480 --> 00:01:57,660 So we're going to take the reviewer ID and join with the reviewers table based off of that ID. 34 00:01:57,690 --> 00:02:03,760 And it's as simple as just repeating the join keyword and I'll be explicit and say inner join. 35 00:02:03,780 --> 00:02:04,680 You don't have to. 36 00:02:06,480 --> 00:02:08,110 We're going to join on. 37 00:02:08,130 --> 00:02:19,580 Well, first join the reviewers table on where the reviews dot reviewer ID, which we've done this before. 38 00:02:19,590 --> 00:02:29,790 It's the same query, the same join we did here is equal to reviewer dot ID or reviewers ID. 39 00:02:30,060 --> 00:02:31,500 So if I do this. 40 00:02:33,220 --> 00:02:33,550 Hoof. 41 00:02:33,670 --> 00:02:34,900 There's a lot to look at. 42 00:02:35,050 --> 00:02:36,400 If I zoom way out. 43 00:02:36,550 --> 00:02:44,620 What we should see is for every review that was left, we have all the information from the series title, 44 00:02:44,620 --> 00:02:48,910 release your genre and all the information of the reviewer. 45 00:02:48,910 --> 00:02:52,660 But we just want first name and last name and title and rating. 46 00:02:53,210 --> 00:02:53,440 Right. 47 00:02:53,440 --> 00:02:55,030 We want to make this. 48 00:02:55,820 --> 00:02:57,530 So let's refine it a bit more. 49 00:02:58,170 --> 00:03:06,420 Select title rating and then can cat first name and last name and that should do it. 50 00:03:06,660 --> 00:03:12,960 Well, if we label this as reviewer or something like that, run this query. 51 00:03:14,610 --> 00:03:18,510 Oh, I should have concatenated a space, but otherwise we're pretty much there. 52 00:03:19,050 --> 00:03:20,580 So let's add that space in. 53 00:03:22,910 --> 00:03:23,510 OC. 54 00:03:25,660 --> 00:03:31,540 And now we have every review filled in with the title that the review belongs to or is associated with 55 00:03:31,540 --> 00:03:34,210 and the reviewer who left the review. 56 00:03:34,690 --> 00:03:42,820 And so we did that by filling in based upon the series ID and the reviewer ID with two joints. 57 00:03:43,120 --> 00:03:45,600 Now, again, I said the order doesn't really matter. 58 00:03:45,610 --> 00:03:53,380 We could rewrite this to start with series and then join with reviews on reviews that series ID, equal 59 00:03:53,410 --> 00:03:56,710 series that ID and then enjoin reviewers. 60 00:03:56,710 --> 00:03:58,390 And this still should work fine. 61 00:03:58,690 --> 00:04:00,190 Same end result. 62 00:04:01,180 --> 00:04:11,380 We could also go the other way around and start with selecting from reviewers in or join on reviews. 63 00:04:11,740 --> 00:04:14,200 And we're going to do reviews dot. 64 00:04:15,400 --> 00:04:21,180 Reviewer's ID equals reviewer ID. 65 00:04:21,220 --> 00:04:25,270 Man, I'm getting tired of saying review and reviewer, and then we'll enter. 66 00:04:25,270 --> 00:04:34,960 Join that on series where the reviews dot series ID equals series dot ID. 67 00:04:35,680 --> 00:04:38,500 So as long as we're joining the same the tables together. 68 00:04:38,530 --> 00:04:40,690 Oh, I have a mistake here. 69 00:04:40,690 --> 00:04:42,550 Reviewer ID there we are. 70 00:04:42,580 --> 00:04:44,680 And I need an SX here as well. 71 00:04:44,710 --> 00:04:45,760 Getting sloppy there. 72 00:04:45,880 --> 00:04:51,940 But what I was trying to say before I kept making errors is that we as long as we join the same three 73 00:04:51,940 --> 00:04:54,590 tables on the same logic, right. 74 00:04:54,640 --> 00:05:01,390 Using the same reviewer ID equals reviewers, date ID series ID equals series that ID the order doesn't 75 00:05:01,390 --> 00:05:02,170 matter now. 76 00:05:02,170 --> 00:05:05,770 The order would matter if we were going to do like a left join versus a right join. 77 00:05:05,770 --> 00:05:07,960 It doesn't matter in this situation at all. 78 00:05:07,960 --> 00:05:10,780 And now we are done with this exercise. 79 00:05:10,780 --> 00:05:14,680 So next we're going to get more practice working with relationships. 80 00:05:14,680 --> 00:05:22,270 Many to many one to many join tables, writing, joins in or join left and all that fun stuff with a 81 00:05:22,270 --> 00:05:26,740 totally different little project, if you want to call it a project, a different data set. 82 00:05:26,740 --> 00:05:27,580 So that's coming up.