WEBVTT 1 00:00:00.350 --> 00:00:01.910 Hi guys, and welcome back. 2 00:00:01.910 --> 00:00:04.571 In this video we're going to start learning about JOINs 3 00:00:04.571 --> 00:00:07.420 and we're going to write one here in DB Browser, 4 00:00:07.420 --> 00:00:09.910 and then go over to our editor and write it there 5 00:00:09.910 --> 00:00:12.660 so we can extract movie title when we're showing the 6 00:00:12.660 --> 00:00:14.550 watched movie information. 7 00:00:14.550 --> 00:00:15.650 So, let's get started. 8 00:00:16.530 --> 00:00:18.390 Here we've got DB Browser and as you can see, 9 00:00:18.390 --> 00:00:21.180 we've got four movies in there at the moment. 10 00:00:21.180 --> 00:00:24.090 Then in the users table we've got two users, 11 00:00:24.090 --> 00:00:26.870 Jose and Rolf, and finally in the watched table 12 00:00:26.870 --> 00:00:28.320 we've got nothing there at the moment. 13 00:00:28.320 --> 00:00:31.280 We're gonna start off by adding a new record, 14 00:00:31.280 --> 00:00:33.943 and here we're gonna put, for example, 15 00:00:33.943 --> 00:00:36.930 Jose and watched movie one. 16 00:00:36.930 --> 00:00:40.500 Then a new record, and also Jose and watched movie, 17 00:00:40.500 --> 00:00:42.803 for example, three, let's say. 18 00:00:43.750 --> 00:00:46.130 So, we've got The Matrix and Gone Girl, 19 00:00:46.130 --> 00:00:49.870 and we're gonna make Rolf watch Mulan with id five, 20 00:00:49.870 --> 00:00:51.520 so we'll add the new record there 21 00:00:52.490 --> 00:00:56.109 and we've got Rolf and movie five. 22 00:00:56.109 --> 00:00:57.130 So, there we go. 23 00:00:57.130 --> 00:00:59.150 We've got our data and I know that you guys 24 00:00:59.150 --> 00:01:01.120 already know how to do this with SQL, 25 00:01:01.120 --> 00:01:02.520 that's why I'm just doing it like this, 26 00:01:02.520 --> 00:01:04.530 but of course you could run the insert commands yourself 27 00:01:04.530 --> 00:01:06.570 if you wanted to, that'd be totally fine. 28 00:01:06.570 --> 00:01:09.290 So, now let's go over to execute SQL 29 00:01:09.290 --> 00:01:12.260 and here is where we're gonna write our JOIN. 30 00:01:12.260 --> 00:01:14.750 So, we're gonna start with a SELECT * 31 00:01:14.750 --> 00:01:16.980 and remember that when we perform a JOIN 32 00:01:16.980 --> 00:01:19.610 we can select columns from an individual table 33 00:01:19.610 --> 00:01:21.240 or we can select individual columns, 34 00:01:21.240 --> 00:01:22.630 we're gonna start with everything for now 35 00:01:22.630 --> 00:01:25.520 just so we can see what the JOIN is producing. 36 00:01:25.520 --> 00:01:27.702 Then we're gonna do FROM movies 37 00:01:27.702 --> 00:01:32.702 and then we're gonna JOIN on watched, for example. 38 00:01:32.890 --> 00:01:37.579 Now, what links movies to the watched table is the movie_id 39 00:01:37.579 --> 00:01:40.399 in the movies table and the movie_id column 40 00:01:40.399 --> 00:01:41.799 in the watched table. 41 00:01:41.799 --> 00:01:45.882 So we'll do ON movies.id = watched.movie_id. 42 00:01:47.190 --> 00:01:49.930 Okay, so we can start with this, we can run it, 43 00:01:49.930 --> 00:01:52.400 and then you'll see, if I bring this down here, 44 00:01:52.400 --> 00:01:56.220 that we have ourselves some information coming back. 45 00:01:56.220 --> 00:01:58.210 We've got the data from the movies table, 46 00:01:58.210 --> 00:01:59.410 the columns from the movies table, 47 00:01:59.410 --> 00:02:01.610 id, title and release_timestamp, 48 00:02:01.610 --> 00:02:05.882 then we've joined so that we get everything here 49 00:02:05.882 --> 00:02:08.870 that has something from the right, 50 00:02:08.870 --> 00:02:12.270 and so we are then matching over and getting the username 51 00:02:12.270 --> 00:02:14.630 and the movie_id of course, which should match. 52 00:02:14.630 --> 00:02:19.630 Now, the thing is, this here evaluates, essentially, 53 00:02:19.930 --> 00:02:21.890 to this table down here. 54 00:02:21.890 --> 00:02:25.821 So now we can JOIN the result with something else. 55 00:02:25.821 --> 00:02:28.480 We can go and JOIN that 56 00:02:28.480 --> 00:02:33.480 ON users.username = watched.user_username. 57 00:02:36.540 --> 00:02:38.630 So, we're selecting FROM movies, 58 00:02:38.630 --> 00:02:41.500 that is the first level of results. 59 00:02:41.500 --> 00:02:45.310 Then we are essentially joining to it the watched table 60 00:02:45.310 --> 00:02:47.060 we're appending that at the end, 61 00:02:47.060 --> 00:02:49.140 and finally we're appending at the end of that 62 00:02:49.140 --> 00:02:52.090 the users table where this matches. 63 00:02:52.090 --> 00:02:53.590 So, we can press play again, 64 00:02:53.590 --> 00:02:54.640 and now you can see that we get 65 00:02:54.640 --> 00:02:55.940 the username there at the back. 66 00:02:55.940 --> 00:02:57.870 Of course, this is not all that useful, 67 00:02:57.870 --> 00:02:58.920 like we've said a few times, 68 00:02:58.920 --> 00:03:00.590 because we've got the username there 69 00:03:00.590 --> 00:03:03.090 that matches the foreign key exactly, 70 00:03:03.090 --> 00:03:05.060 but you could have more useful information here, 71 00:03:05.060 --> 00:03:07.101 more columns, we just decided not to put them in 72 00:03:07.101 --> 00:03:09.830 because it's not really necessary for the example. 73 00:03:09.830 --> 00:03:12.730 Okay, finally, what we can do is we can filter 74 00:03:12.730 --> 00:03:15.290 on any of the columns that we have selected, 75 00:03:15.290 --> 00:03:18.530 so we can filter on movie id, title, release_timestamp, 76 00:03:18.530 --> 00:03:22.330 we can filter on watched user_username or watched movie_id 77 00:03:22.330 --> 00:03:24.333 or we can filter on users.username. 78 00:03:24.333 --> 00:03:25.920 And that's what we're gonna do here, 79 00:03:25.920 --> 00:03:29.200 we're gonna say users.username = 80 00:03:29.200 --> 00:03:31.150 and we're gonna say, for example, Jose. 81 00:03:32.660 --> 00:03:34.540 Now, we just get the information coming back 82 00:03:34.540 --> 00:03:36.340 for the user Jose, 83 00:03:36.340 --> 00:03:38.670 we can replace that for anything else if we want. 84 00:03:38.670 --> 00:03:43.670 So, in our code, that is gonna be the question mark. 85 00:03:43.710 --> 00:03:45.267 All right, so let's copy that, 86 00:03:45.267 --> 00:03:50.267 and go over to the code and we can use it in there. 87 00:03:50.560 --> 00:03:52.240 By the way, if you're confused at this point 88 00:03:52.240 --> 00:03:55.070 because of this query, don't worry too much, 89 00:03:55.070 --> 00:03:57.410 this is by far the most complicated query we've written 90 00:03:57.410 --> 00:03:59.410 so hopefully it makes some sense, 91 00:03:59.410 --> 00:04:00.939 but if it's very confusing, 92 00:04:00.939 --> 00:04:04.170 try to write a few more queries that join different tables, 93 00:04:04.170 --> 00:04:06.436 maybe try a different example that's not movies, 94 00:04:06.436 --> 00:04:10.790 for example, I don't know, books or something like that, 95 00:04:10.790 --> 00:04:13.178 or your power tools from your shed, 96 00:04:13.178 --> 00:04:16.510 whatever you like as an example, try that out, 97 00:04:16.510 --> 00:04:19.398 as doing more examples is going to greatly help 98 00:04:19.398 --> 00:04:20.600 in learning these things. 99 00:04:20.600 --> 00:04:23.140 It's gonna make it click much more easily. 100 00:04:23.140 --> 00:04:25.610 So, then query lets us get the movie data 101 00:04:25.610 --> 00:04:28.550 of movies that a certain user has watched. 102 00:04:28.550 --> 00:04:31.360 So we can go ahead and make some changes 103 00:04:31.360 --> 00:04:33.647 over in database.py. 104 00:04:33.647 --> 00:04:35.540 SELECT_WATCHED_MOVIES, at the moment, 105 00:04:35.540 --> 00:04:38.270 is selecting the columns from the watched table, 106 00:04:38.270 --> 00:04:39.930 but of course that doesn't return the information 107 00:04:39.930 --> 00:04:41.040 that it wants. 108 00:04:41.040 --> 00:04:45.496 So, if we simply replace that for our new query, 109 00:04:45.496 --> 00:04:49.174 then we're going to be selecting, essentially, 110 00:04:49.174 --> 00:04:51.240 and I'm gonna bring this over to this line, 111 00:04:51.240 --> 00:04:53.890 we're selecting everything from the movies table, 112 00:04:53.890 --> 00:04:55.820 the watched table and the users table 113 00:04:55.820 --> 00:04:58.480 and at the end of the day what we do end up with, 114 00:04:58.480 --> 00:05:01.780 as we saw in DB Browser is the information 115 00:05:01.780 --> 00:05:04.810 related to movies that a particular user has watched, 116 00:05:04.810 --> 00:05:07.000 as well as a bunch of other information 117 00:05:07.000 --> 00:05:08.896 that we may not think is relevant. 118 00:05:08.896 --> 00:05:11.470 Because this query is in charge, 119 00:05:11.470 --> 00:05:13.050 or has been in charge up to now, 120 00:05:13.050 --> 00:05:16.230 of selecting movie data that has been watched, 121 00:05:16.230 --> 00:05:19.940 maybe instead of selecting * we wanna do movies.*. 122 00:05:21.289 --> 00:05:22.420 And if we do this, 123 00:05:22.420 --> 00:05:25.080 what we're gonna get back out of this entire query 124 00:05:25.080 --> 00:05:28.640 is movie data that a user has watched. 125 00:05:28.640 --> 00:05:30.680 So, again, if we go back to DB Browser 126 00:05:30.680 --> 00:05:34.700 and I do movies.* you'll see that what we'll get back 127 00:05:34.700 --> 00:05:39.410 is the movie columns that have been watched by Jose. 128 00:05:39.410 --> 00:05:41.646 So, if I press play, then of course I have to 129 00:05:41.646 --> 00:05:46.170 put in this string in here and we get that movie data now. 130 00:05:46.170 --> 00:05:48.710 So, there we go, there we've got it. 131 00:05:48.710 --> 00:05:49.810 So, of course we do want to keep 132 00:05:49.810 --> 00:05:50.730 the question mark down there 133 00:05:50.730 --> 00:05:54.440 because we're gonna be passing an argument in our code. 134 00:05:54.440 --> 00:05:56.390 Remember that in app.py 135 00:05:56.390 --> 00:05:59.840 we have a print_watched_movie_list function 136 00:05:59.840 --> 00:06:03.760 that displays the movie information that a user has watched, 137 00:06:03.760 --> 00:06:07.470 but this function was specifically made for stage two, 138 00:06:07.470 --> 00:06:09.620 where movies were in their own table 139 00:06:09.620 --> 00:06:12.480 alongside the username that watched them. 140 00:06:12.480 --> 00:06:16.360 Now, get_watched_movies is returning movie columns, 141 00:06:16.360 --> 00:06:19.110 exactly the same thing that, for example, 142 00:06:19.110 --> 00:06:21.660 get_movies is returning. 143 00:06:21.660 --> 00:06:24.900 Therefore, we no longer need print_watched_movie list, 144 00:06:24.900 --> 00:06:29.050 now we can simply use the print_movie_list 145 00:06:29.050 --> 00:06:30.773 that we have already. 146 00:06:30.773 --> 00:06:33.274 So, we're gonna delete that function, 147 00:06:33.274 --> 00:06:36.180 then coming back down over here to the menu, 148 00:06:36.180 --> 00:06:37.510 in this section here we're gonna 149 00:06:37.510 --> 00:06:39.540 make a couple of changes as well. 150 00:06:39.540 --> 00:06:41.520 So, we want to get the username because 151 00:06:41.520 --> 00:06:43.860 we need that for getting the watched movies, 152 00:06:43.860 --> 00:06:48.045 and then we simply pass the movies to print_movie_list 153 00:06:48.045 --> 00:06:51.240 and here you can do something like username 154 00:06:51.240 --> 00:06:52.730 and then what's gonna happen is that is 155 00:06:52.730 --> 00:06:55.430 gonna be passed over to this heading 156 00:06:55.430 --> 00:06:58.090 and it will show, for example, Jose movies, 157 00:06:58.090 --> 00:07:02.320 you can do something a bit fancier like something like this, 158 00:07:02.320 --> 00:07:05.060 with the curly braces there for the up string, 159 00:07:05.060 --> 00:07:09.769 watched movies, for example, like that, 160 00:07:09.769 --> 00:07:12.470 or you can just do what we're gonna do 161 00:07:12.470 --> 00:07:14.320 which is just put watched movies. 162 00:07:14.320 --> 00:07:16.670 After all, they've just entered the username, 163 00:07:16.670 --> 00:07:18.990 so they're gonna know who these watched movies are for, 164 00:07:18.990 --> 00:07:20.150 we can just put watched movies, 165 00:07:20.150 --> 00:07:21.500 that's gonna go over into the heading 166 00:07:21.500 --> 00:07:23.610 and show watched movies. 167 00:07:23.610 --> 00:07:26.090 Again, feel free to change things around if you want, 168 00:07:26.090 --> 00:07:27.550 you don't want to put "watched" in there, 169 00:07:27.550 --> 00:07:29.080 you can put something else in there if you want 170 00:07:29.080 --> 00:07:30.770 and that's totally fine. 171 00:07:30.770 --> 00:07:33.520 Now, something important is that sometimes users 172 00:07:33.520 --> 00:07:36.110 are not gonna have any watched movies, 173 00:07:36.110 --> 00:07:39.970 and so when we get this, that's gonna be an empty list. 174 00:07:39.970 --> 00:07:42.190 We can do something like this, 175 00:07:42.190 --> 00:07:44.769 if movies then print the movie list 176 00:07:44.769 --> 00:07:48.467 otherwise, print something like, 177 00:07:48.467 --> 00:07:52.227 "That user has no watched movies." 178 00:07:53.490 --> 00:07:56.960 Or if you want to write something in slightly better English 179 00:07:56.960 --> 00:08:00.090 you can say "Has watched no movies yet!" 180 00:08:00.090 --> 00:08:01.090 Something like that. 181 00:08:01.090 --> 00:08:03.771 Again, these strings are totally up to you. 182 00:08:03.771 --> 00:08:06.100 You can do this same thing with these 183 00:08:06.100 --> 00:08:07.920 other branches as well if you want, 184 00:08:07.920 --> 00:08:09.870 that's gonna make your programme a little bit nicer 185 00:08:09.870 --> 00:08:10.880 but it would all be quite similar 186 00:08:10.880 --> 00:08:13.160 so we're not gonna do that here. 187 00:08:13.160 --> 00:08:15.180 I'd say that this branch of code down here 188 00:08:15.180 --> 00:08:16.270 is getting a little bit long, 189 00:08:16.270 --> 00:08:21.020 so I'm going to cut it and put it into a new function, 190 00:08:21.020 --> 00:08:23.040 so we're gonna do prompt_show_watched_movies 191 00:08:25.710 --> 00:08:27.160 and we're gonna put everything in there, 192 00:08:27.160 --> 00:08:30.560 remembering the indentation has to be correct 193 00:08:30.560 --> 00:08:33.160 and then call that there. 194 00:08:33.160 --> 00:08:35.630 All right, that's about it for this video. 195 00:08:35.630 --> 00:08:36.480 I hope you're still with me, 196 00:08:36.480 --> 00:08:38.060 even if a little bit confused, 197 00:08:38.060 --> 00:08:40.650 remember to try different examples if you can, 198 00:08:40.650 --> 00:08:43.040 build a couple apps that use a similar structure 199 00:08:43.040 --> 00:08:44.250 to what we've got here, 200 00:08:44.250 --> 00:08:46.899 and that's really gonna help cement this knowledge. 201 00:08:46.899 --> 00:08:48.840 Thank you guys for watching, thanks for joining me 202 00:08:48.840 --> 00:08:51.253 in this video, and I'll see you in the next one.