WEBVTT 00:00.130 --> 00:05.380 OK we're at the final video here the final challenge and this is the one that puts everything together 00:06.010 --> 00:10.850 are really more what it's doing is putting together all three tables that we have. 00:11.130 --> 00:14.410 So what we're actually getting out of it is not that impressive right. 00:14.490 --> 00:18.670 We're not doing any crazy analytics or anything like the previous one. 00:18.740 --> 00:21.440 Not that that was too crazy but nothing like that. 00:21.540 --> 00:29.040 All that we're doing is putting the title of a movie here of a series and then every rating that it 00:29.040 --> 00:32.260 got along with the reviewer who wrote that reading. 00:32.700 --> 00:36.020 So we've done the first part up until here right. 00:36.050 --> 00:40.890 We've done title and writing together or where we've done reviewer and writing together but now we're 00:40.890 --> 00:45.700 going to put them all together so that this is the information for one review right. 00:45.840 --> 00:48.470 It's the movie that was the series that was reviewed. 00:48.510 --> 00:50.250 It was the person who reviewed it. 00:50.460 --> 00:56.240 Notice that we can cast them into one reviewer instead of first name and last name but that's secondary 00:56.250 --> 00:57.240 at the end. 00:57.240 --> 01:02.080 So we have the series title the reviewers name and then the rating they gave it. 01:02.220 --> 01:05.910 So we're basically just trying to take. 01:06.630 --> 01:10.160 Let's do it here take this and fill in the blanks. 01:10.170 --> 01:14.350 So instead of serious ID we're going to get the name of the series instead of the reviewer. 01:14.430 --> 01:16.480 We're going to get the name of the reviewer. 01:17.070 --> 01:17.740 OK. 01:17.970 --> 01:24.990 So let's go ahead and start over here with our handy dandy comment and we are on what is this seven 01:27.170 --> 01:32.940 six Yes seven three tables. 01:33.810 --> 01:34.480 OK. 01:34.730 --> 01:37.160 So we can start off with any of the tables. 01:37.250 --> 01:38.530 We're going to join them all together. 01:38.540 --> 01:47.390 But I'll start with reviewers so we'll select start from reviewers just like that except I need from 01:49.630 --> 01:51.250 boring we've done this before. 01:51.250 --> 01:52.650 Blah blah blah blah blah. 01:52.660 --> 01:57.490 So the first step is to join in with the reviews table. 01:58.000 --> 02:07.130 So we'll have review hers along with their reviews along with the series associated with that review. 02:07.480 --> 02:11.300 And we're not going to worry about you know people who haven't written reviews. 02:11.350 --> 02:12.060 That's fine. 02:12.070 --> 02:13.290 We're ignoring that. 02:13.810 --> 02:14.450 OK. 02:14.800 --> 02:19.540 So to do this let's start off first of all by joining it with 02:22.370 --> 02:38.480 join reviews on reviewers ID because reviews it and then we need review were ID so fun to write and 02:38.480 --> 02:47.290 we do that and we see things like OK Thomas Stoneman gave an 8.0 to some movie or some series idea for 02:47.300 --> 02:48.150 one. 02:48.170 --> 02:51.870 He also gave an eight point one to whatever has a D to and so on. 02:51.890 --> 03:00.910 So we're getting close ish Now what we need to do is also throw on the series data over here and the 03:00.920 --> 03:02.840 first thing I'll do is just make this explicit. 03:02.840 --> 03:04.220 It's an inner join. 03:04.370 --> 03:10.790 So we're going to do another joint and it's really easy at least syntactically it's easy. 03:10.790 --> 03:12.790 We just write inner join again. 03:12.950 --> 03:14.740 So what are we joining on this time. 03:15.020 --> 03:19.110 Well we're now going to join on the only thing that's left which is Series. 03:19.340 --> 03:24.110 So we took reviewers and reviews said Sandridge them together where they intersect. 03:24.680 --> 03:33.550 Now what we're going to do is join a series where the series A.D. is equal to series ID here inside 03:33.550 --> 03:34.730 of the reviews. 03:35.360 --> 03:48.020 So let's do inner join series on series start ID equals the reviews review Eric dot series underscore 03:48.020 --> 03:52.190 ID such a nightmare trying to speak all this out loud. 03:52.700 --> 03:54.620 But let's step through this. 03:55.340 --> 04:03.620 OK so we have sort of this middle table let's say which is right here we have I.D. rating in series 04:03.620 --> 04:10.130 ID and reviewer ID for our review that every review has that and what we started off was joining it 04:10.160 --> 04:13.280 with the relevant reviewers. 04:13.340 --> 04:20.270 Now we're going to also take the series idea and find all matching series and stick them over here and 04:20.270 --> 04:21.670 it's going to be a messy table. 04:21.830 --> 04:23.990 It's gonna get all collapsed and screwed up. 04:24.080 --> 04:36.780 So let's filter out some things so let's only work with the title the rating first name and last name. 04:41.810 --> 04:43.530 OK so we're getting close now. 04:43.770 --> 04:47.040 So we're pretty much there we have all of the data together. 04:47.510 --> 04:55.790 So we have Archer has an 8.0 rating from Thomas Stoneman Thomas Stoneman also gave Arrested Development 04:55.790 --> 05:02.540 at a point one and he gave Bob's Burgers at 7 Pernod and then we can see OK we have Archer again down 05:02.540 --> 05:05.590 here from Wyatt Scaggs who gave it to seven point five. 05:05.810 --> 05:08.990 So we have all of our data synthesized in one table. 05:08.990 --> 05:13.370 Now what we need to do is concatenate things together over here. 05:13.730 --> 05:21.110 So that's a simple we need to do is instead of doing first name and last name we'll do a king cat first 05:21.110 --> 05:28.840 name comma space last name as a reviewer I think it's what we called it yep. 05:29.810 --> 05:30.490 Okay. 05:30.530 --> 05:32.530 And then there's only one thing that we need to change. 05:32.540 --> 05:38.870 Other than that which is right now things are ordered by a reviewer. 05:38.960 --> 05:42.460 Let's order them by title which is what we did over here. 05:42.710 --> 05:47.780 So that's just a simple order by title. 05:47.840 --> 05:52.540 Now what should be done if I get rid of that extra semicolon. 05:53.150 --> 05:54.160 And here we go. 05:54.590 --> 06:00.920 So we have Archer all of the ratings for Archer and the people who wrote them are arrested development 06:00.920 --> 06:08.270 all the ratings and people who wrote them and all the way down through all of our series that have reviews 06:08.530 --> 06:09.430 so that's important. 06:09.430 --> 06:11.240 It's only where we have reviews. 06:11.870 --> 06:14.480 So to summarize let's go all the way back here. 06:14.750 --> 06:22.130 What we just did was basically take the reviewers table smash it with reviews table where they match. 06:22.290 --> 06:23.290 It's an inner join. 06:23.300 --> 06:24.790 Or where's the overlap. 06:24.800 --> 06:25.310 Right. 06:25.550 --> 06:32.330 So we take the overlap between them based off of ID here and reviewer ID then we have that second table 06:32.980 --> 06:38.900 that we then joined with series where the series ID match the idea of a series. 06:38.930 --> 06:42.710 So it's a three way to joint statements but three tables together. 06:42.770 --> 06:48.630 And that's why I decided to do many to many relationships after the simpler one too many. 06:48.680 --> 06:50.570 But hopefully it's not too intimidating. 06:50.570 --> 06:53.610 We did a bunch of exercises with them. 06:53.990 --> 06:55.650 You're probably a bit burnt out. 06:55.670 --> 07:01.130 I know I am from recording all of them so I definitely recommend that you take a break for and move 07:01.130 --> 07:01.660 on. 07:01.700 --> 07:06.200 We're going to continue of course with joints and relationships but we're only building from here on 07:06.200 --> 07:06.520 out. 07:06.710 --> 07:08.840 So I can grab it and making it through this part.