WEBVTT 00:07.640 --> 00:10.880 I will call you back again to another lecture in PostgreSQL. 00:10.910 --> 00:15.200 Let's go ahead and look at joining PostgreSQL. 00:15.380 --> 00:18.350 So in the previous lecture we have these tables. 00:18.350 --> 00:24.080 And we're able to assign some fields to some of our students. 00:24.080 --> 00:27.290 And let's go and assign select our from our students. 00:27.290 --> 00:31.100 And then we have this phone assigned to these students. 00:31.100 --> 00:38.150 And let's go ahead and select the next table and select our all from phone. 00:38.690 --> 00:41.810 And we have these phones assigned to these students. 00:41.840 --> 00:51.260 Now I know that you as a beginner in PostgreSQL or in SQL might be like, wow, I want to see maybe 00:51.290 --> 00:59.180 first name Rosalind, George, Lulu and Tubby to be able to have this phone Motorola I made and Samsung 00:59.180 --> 01:04.970 assigned and not just their ID A.D. and that is going to make more sense to us as beginners. 01:05.000 --> 01:09.620 Yes, I also agree with you because that is how it's supposed to be. 01:09.650 --> 01:12.950 I don't just have to assign only the IDs which has seen this. 01:12.980 --> 01:19.310 Now I'm going to assign the first name, and I'm going to join that with the brand of this phone and 01:19.310 --> 01:20.300 then with the model. 01:20.300 --> 01:28.310 Or we just go ahead and assign that with the brand and the price, because the model is still on the 01:28.310 --> 01:29.270 same thing with the brand. 01:29.300 --> 01:32.870 We have Motorola and Motorola moto E play. 01:32.900 --> 01:33.920 We don't just need this. 01:33.920 --> 01:35.540 So let's go ahead and say Russell. 01:35.540 --> 01:41.240 And then the next column is going to be the brand of the phone assigned to a resident or judge. 01:41.240 --> 01:44.060 And then the price of the phone that the student is using. 01:44.060 --> 01:46.520 So this is where the join actually comes together. 01:46.520 --> 01:48.770 So the join helps us to join two tables. 01:48.770 --> 01:52.220 And then we go ahead and have just one particular table. 01:52.220 --> 01:56.600 So we join the table A and table B and they will create a table C. 01:56.630 --> 02:01.580 And that is where you can be able to see each of the students with a first name or the last name. 02:01.580 --> 02:02.530 And you anyone you choose. 02:02.530 --> 02:07.810 We can actually do the first and last name, and then we have the brand of the phone and the prices. 02:07.810 --> 02:11.980 And you can also do the first name and the brand of the phone and loan anyone. 02:11.980 --> 02:13.570 I'm going to show you how to do that. 02:13.570 --> 02:16.000 So let's go ahead and get started immediately. 02:16.810 --> 02:25.240 So let's go ahead and see for us to train that I'm going to select AU from the students table. 02:25.240 --> 02:28.060 So go ahead and select AU from students. 02:28.060 --> 02:30.370 And I'm not going to press enter right in here. 02:30.370 --> 02:32.110 And I'm going to put the semicolon at the end. 02:32.140 --> 02:35.620 So go ahead and say join. 02:36.220 --> 02:36.670 Alright. 02:36.670 --> 02:39.310 So I'm going to use an uppercase because that's a keyword. 02:39.310 --> 02:41.110 So join. 02:41.110 --> 02:42.940 Then I'm going to join the phone. 02:42.970 --> 02:43.390 All right. 02:43.420 --> 02:47.530 So I'm going to join the table phone I'm going to say join phone. 02:47.530 --> 02:51.280 And uh I'm going to use the on keyword. 02:51.310 --> 02:55.780 Now the on keyword we have asked to create a new table. 02:55.780 --> 03:01.030 And this table is going to be the student table and the phone table. 03:01.030 --> 03:06.460 And if we want to do that, what are we going to do is we're going to create a new table within the 03:06.460 --> 03:12.940 students table, including include the font ID and the the font table. 03:12.940 --> 03:15.910 Include the font ID uh, as well. 03:15.910 --> 03:17.260 So the font ID is this. 03:17.260 --> 03:21.760 And uh for the stress table, this is for the font ID we're going to use. 03:21.760 --> 03:26.710 So let me go ahead and say students dot. 03:26.740 --> 03:37.900 Then go ahead and say font underscore ID phone underscore ID is equal to then phone dot. 03:37.930 --> 03:41.680 Then this is the ID I'm going to match that with that idea. 03:41.710 --> 03:48.190 Now I'm go ahead and put this uh semicolon at the end and hit enter. 03:48.190 --> 03:51.970 And now I'm going to expand this. 03:52.000 --> 03:52.570 All right. 03:52.600 --> 03:54.400 Go ahead and expand this. 03:54.520 --> 03:58.480 And now you can see that I have match these two tables. 03:59.050 --> 04:00.550 I've mentioned this to tables. 04:00.550 --> 04:03.640 And uh you can see what I have right in here. 04:03.640 --> 04:09.730 I have this table of our students and this table of phone in one new table. 04:09.760 --> 04:10.630 Check it out. 04:10.660 --> 04:11.470 The phone. 04:11.500 --> 04:13.690 The student level is this. 04:13.690 --> 04:16.360 And the phone table is this. 04:16.360 --> 04:18.580 And I hope that is cool. 04:18.610 --> 04:27.310 Now that one looks really so much long and you may be like, whoa, this is not really looking good. 04:27.310 --> 04:33.220 What do we need now is how can we just take only the name of the student? 04:33.250 --> 04:38.020 Let me say, uh, Lulu, Toby and George. 04:38.020 --> 04:40.510 But before we do that, check out the students we have here. 04:40.510 --> 04:46.150 We have four of the students, and we have one of the student, which is our Russian land or Mayweather. 04:46.180 --> 04:50.590 No phone was assigned to this student and we can check on the new table. 04:50.620 --> 04:57.490 Would not have Rosalind or Mayweather here because no phone was assigned to him or her. 04:57.520 --> 04:58.630 That is a hair. 04:58.660 --> 04:59.200 All right. 04:59.230 --> 05:00.400 So all right, is she. 05:00.430 --> 05:05.940 So no phone was assigned to her, and, uh, only the people who have phones assigned to them. 05:05.940 --> 05:07.650 We are listed out. 05:07.650 --> 05:11.400 So in this way, you can see that the foreign key actually works. 05:11.400 --> 05:14.250 And we can also join this table. 05:14.280 --> 05:19.620 Now I'm going to trim this table so we can just have the first and the last name and then the brand 05:19.620 --> 05:22.410 of the phone and the price of the phone that we assigned to them. 05:22.410 --> 05:24.210 There.while I show you how to do that. 05:24.600 --> 05:26.160 So I'll go right in here. 05:26.160 --> 05:31.110 And all I need to do is go ahead and specify the columns I want. 05:31.140 --> 05:44.790 So go ahead and say select a no longer all, but I'll go ahead and say select a students dot first underscore 05:44.790 --> 05:47.040 name and put a comma. 05:47.070 --> 05:58.560 If I want to select the last name two I'll go ahead and say students dot last underscore name and put 05:58.590 --> 06:01.710 a comma because I need these two rows okay. 06:01.740 --> 06:04.500 You can also specify just one because I just needed two. 06:04.530 --> 06:05.280 No problem. 06:05.280 --> 06:11.430 And then I'll go ahead and go over the phone and say phone dots. 06:11.460 --> 06:21.390 I need a brand of the phone phone brand and put a comma and then phone dot price. 06:21.480 --> 06:21.780 All right. 06:21.810 --> 06:26.340 I don't need the model because this model is too long and it tells the specification of the same brand. 06:26.340 --> 06:28.350 So there is no need putting that there. 06:28.350 --> 06:31.050 And then I'll go ahead and hit enter without a semicolon. 06:31.080 --> 06:36.120 Then I'm going to select that from the students. 06:36.150 --> 06:38.670 Go ahead and select that from the students table. 06:38.670 --> 06:43.320 And I'll go ahead and join that with the phone table. 06:43.320 --> 06:45.420 And I'm going to say phone. 06:45.420 --> 06:50.880 And uh using the same all to create a new table. 06:50.910 --> 06:53.850 Go ahead and use the all keyword to create a new table. 06:53.850 --> 07:05.300 And that table is a table of uh, students dot phone underscore ID equal to 07:07.220 --> 07:10.250 phone.id. 07:10.280 --> 07:12.530 Then put a semicolon at the end of this. 07:12.530 --> 07:15.620 And then we go ahead and hit enter. 07:15.620 --> 07:19.220 And now we can see we have first name rural. 07:19.220 --> 07:23.630 And that is the last name brand phone Motorola. 07:23.630 --> 07:25.070 And the price is DS. 07:25.070 --> 07:29.210 And Toby Acosta has a brand of image. 07:29.210 --> 07:30.800 And this is a price of the phone. 07:30.800 --> 07:36.290 And George Steve has a brand of Samsung with a price of this Samsung phone. 07:36.290 --> 07:42.410 So in that way you can be able to join two tables using the join keyword and you can see the step by 07:42.440 --> 07:42.860 step. 07:42.860 --> 07:44.360 And then it looks so beautiful. 07:44.390 --> 07:45.710 Go ahead and play around with these. 07:45.710 --> 07:46.250 Check it out. 07:46.250 --> 07:49.760 And if you have any question you go ahead and use the question and answer section. 07:49.760 --> 07:52.610 And I'm going to see you in the next video lecture. 07:52.610 --> 07:55.310 So remember that this is the part of the foreign key. 07:55.340 --> 07:58.670 Thank you and see you in the next video lecture.