WEBVTT 00:07.460 --> 00:10.100 I will call you back again to another video lecture on MySQL. 00:10.100 --> 00:15.380 And in this video lecture, let's go ahead and look at another example of Self-join in MySQL. 00:15.410 --> 00:19.730 Our previous lecture, we were able to look at the students table right in here before us. 00:19.730 --> 00:25.430 We have a table called the employees table and other employees towards that table we have created in 00:25.430 --> 00:26.810 the previous lectures. 00:26.810 --> 00:33.260 Then I went ahead and added affiliate ID, and I just used this code already here to create that. 00:33.260 --> 00:35.300 So go ahead and put that down. 00:35.750 --> 00:39.410 Alright, so within our self-join is used to display the hierarchy of data in our tables. 00:39.410 --> 00:45.410 And we also use the Self-join to actually join a copy of table to itself. 00:45.410 --> 00:47.690 And then go ahead and compare the rules. 00:47.690 --> 00:52.040 So now I have this employee table one here, and I added affiliate. 00:52.040 --> 01:00.920 And what I want to display here is that I want each of these employees from Kizito Messi, George Chief 01:00.920 --> 01:04.660 and MLA to be referred Refreshed by Michael. 01:04.660 --> 01:06.760 So I want Michael to be the one referring them. 01:06.760 --> 01:09.880 So Michael is referring everybody. 01:09.880 --> 01:18.100 And then the affiliate IDs are going to be that of Michael and the Michael employee ID is one. 01:18.100 --> 01:24.340 So whenever the athlete ID is equal to 2 or 3, four, five, we'll go ahead and put the. 01:24.880 --> 01:28.090 So we're going to put Michael employee ID there. 01:28.120 --> 01:36.580 So if we have employee ID equal to two then the affiliate ID is going to be equal to one. 01:36.610 --> 01:37.150 All right. 01:37.150 --> 01:39.730 So in that case that is where we're going to do that. 01:39.760 --> 01:42.670 Then let's go ahead and remove all these. 01:42.700 --> 01:47.290 Then we go ahead and update the employees affiliate column. 01:47.290 --> 01:49.660 And now let's go ahead and say update. 01:49.840 --> 01:53.440 And then we'll go ahead and say employees. 01:53.440 --> 01:57.820 And then we go ahead and set affiliates. 01:59.680 --> 02:09.000 So set affiliates underscore ID is equal to one because we are using the athlete ID of Michael, then 02:09.030 --> 02:15.120 we go ahead and say, isn't a VR class where the employee. 02:15.240 --> 02:26.190 Oh, that should be a lower case where the employee underscore ID is equal to two and so on. 02:26.190 --> 02:29.220 So we're going to order 2345678. 02:29.220 --> 02:32.550 And so now let's go ahead and update the very first one and check it out. 02:32.550 --> 02:33.600 So that is one. 02:33.600 --> 02:37.080 So the athlete ID is a reflection of the Michael. 02:37.110 --> 02:39.750 Then we'll go ahead and see. 02:39.750 --> 02:43.290 This is a three overhead and that is a one. 02:43.920 --> 02:46.470 And the four go ahead and execute that. 02:46.920 --> 02:49.170 And that is one again. 02:49.170 --> 02:54.120 Now what if we say that George actually referred to Steve Case. 02:54.150 --> 03:00.120 Let's go ahead and say that Michael referred George referred to Messi and George and George went ahead 03:00.150 --> 03:01.320 and referred Steve. 03:01.350 --> 03:07.440 If we want to do that, we go ahead and set where the athlete ID is equal to four. 03:07.440 --> 03:10.490 then the employee ID is equal to five. 03:10.520 --> 03:20.180 This is so you can see that the employee ID for judge is now displayed as an athlete ID for Steve. 03:20.210 --> 03:20.870 Okay. 03:20.900 --> 03:26.030 And um, we want to say maybe judge also ordered a memoir. 03:26.060 --> 03:29.480 So go ahead and change this employee ID and let's check it out. 03:29.510 --> 03:35.480 So this is amazing waterfront by Michael I'm the judge, not Steve. 03:35.480 --> 03:36.320 And the memoir. 03:36.350 --> 03:39.170 And now we want to join this table. 03:39.200 --> 03:42.380 Let's go ahead and join this table using the inner join. 03:42.380 --> 03:48.830 So we can be able to actually match all these, uh, people with the people that actually, uh, refer 03:48.830 --> 03:49.010 them. 03:49.040 --> 03:58.820 So what we're going to do is go ahead and uh, select white and select uh oh from we're going to select 03:58.850 --> 04:01.760 all from our employees. 04:03.440 --> 04:03.980 All right. 04:03.980 --> 04:05.510 So we're going to give an alias. 04:05.540 --> 04:09.320 We are alias a employee as a. 04:09.350 --> 04:14.860 So we're going to the same place as a and, uh, in a joint. 04:20.140 --> 04:26.980 So when we use the inner join that we did in the previous lecture and uh c employee. 04:30.400 --> 04:31.930 We're using the alias SB. 04:32.110 --> 04:39.190 Now let's go ahead and uh match these same all in dot affiliates. 04:42.310 --> 04:48.550 And dot affiliates underscore ID somehow I'm not going to make the column the affiliate column with 04:48.730 --> 04:49.720 the employee column. 04:49.720 --> 05:00.370 So affiliate ID equal to B dot uh employees underscore ID put a semicolon at the end of this. 05:00.370 --> 05:03.580 So now go ahead and execute this query. 05:03.730 --> 05:08.410 Let's ensure okay I have this always right in a because I don't always remove this. 05:08.410 --> 05:10.780 So it's always going to display. 05:10.810 --> 05:12.190 Go ahead and take it out. 05:12.190 --> 05:14.550 nuts and let's see what goes on. 05:14.820 --> 05:17.250 So from this error we set out our table. 05:17.280 --> 05:18.390 Employee doesn't exist. 05:18.390 --> 05:21.720 So what we have is employees and not employee right. 05:21.720 --> 05:27.660 So go ahead and execute this query again because we missed the s right right in there. 05:28.410 --> 05:33.960 And now we have right in here um the athlete is in Michael Michael Werner if anybody. 05:33.960 --> 05:37.590 And that is why you can see my athlete's ID for Michael is no. 05:37.620 --> 05:46.380 But athlete ID for George is a one and one because George was referred by Michael and Michael actually 05:46.410 --> 05:51.270 referring George, and Michael is referring Messi and Kizito while George referring and memoir. 05:51.270 --> 05:54.090 And Steve, uh, because you're using inner join. 05:54.090 --> 05:56.520 That is why this is, uh, in ascending order. 05:56.550 --> 06:02.820 If you go ahead and use left, join the square hat and use a left join and check it out so you can see 06:02.820 --> 06:08.520 that our is now displayed according to the IDs and are no longer alphabetical order. 06:08.550 --> 06:08.970 All right. 06:09.000 --> 06:13.200 So if you use the inner join is going to be in a ascending order or alphabetical order. 06:13.230 --> 06:17.070 What if you use left Join its core head and check that out by ID? 06:17.070 --> 06:19.080 And that is what we have right in here. 06:19.110 --> 06:24.960 Now let's go ahead and display only the specific table that we need because we are selecting everything. 06:24.960 --> 06:30.510 And I want the athlete ID and the employee ID are these to be shown at the KPA. 06:30.540 --> 06:36.150 I just want the first name of the employees and the first name of the program that referred them to 06:36.180 --> 06:36.720 match. 06:36.720 --> 06:42.210 So what I'm going to do is that I'm going to remove this select all, and I'm going to say selector 06:42.240 --> 06:49.230 a dot uh, select dot employee or underscore ID. 06:49.620 --> 07:03.210 And um then I'm going to say a dot first underscore name and uh b dot uh first underscore name. 07:03.690 --> 07:03.990 Alright. 07:03.990 --> 07:06.840 Now I'll go ahead and execute this query again. 07:06.840 --> 07:07.650 Let's check it out. 07:07.650 --> 07:11.220 So now I can see that my code was actually referred by nobody. 07:11.220 --> 07:13.230 And that is why we have no why. 07:13.260 --> 07:15.450 Because it was referred by Michael Messi. 07:15.480 --> 07:21.040 Referred by Michael George followed by Michael and I'll Still Refer by George and the memoir referred 07:21.040 --> 07:21.700 by George. 07:21.700 --> 07:22.870 So that is what we have. 07:22.900 --> 07:28.570 But now we can see we have our first name, first name, and now how can we be able to remove these 07:28.570 --> 07:32.920 so that we can just have a referral or affiliate. 07:32.920 --> 07:35.200 So we can just make this to be referral. 07:35.200 --> 07:37.090 So how can we actually do that. 07:37.090 --> 07:42.130 So what do we need to do right here is are we going to concatenate the very first what we have here. 07:42.130 --> 07:49.090 So what I'm going to do is I where I say concat and then I go ahead and close this. 07:49.090 --> 07:55.120 So let me go ahead and bring this down so that this is just going to be in a one line because it's really 07:55.120 --> 07:56.560 extending to the next line. 07:56.560 --> 08:00.910 And I can actually go ahead and bring this one up down as well. 08:00.940 --> 08:01.900 There is no problem. 08:01.900 --> 08:04.990 It's uh for clarity purpose. 08:04.990 --> 08:07.870 Then I go ahead and make this an alias. 08:07.870 --> 08:13.210 I'm going to say S then I can refer them this as a referral. 08:13.990 --> 08:14.350 All right. 08:14.350 --> 08:15.520 So I hope that is cool. 08:15.550 --> 08:19.860 Now if we go ahead and, uh, run this, this query. 08:19.890 --> 08:24.480 So now this is our employee ID, the first name of the employees and the referrers. 08:24.480 --> 08:26.010 So this is not a referrers. 08:26.040 --> 08:29.130 And now if I go ahead and continue to use an inner join. 08:29.970 --> 08:31.350 So use inner join. 08:31.350 --> 08:33.750 And it's going to remove my code up there. 08:33.750 --> 08:38.550 And you can see that judge the four by Michael Messi referred by Michael because it's referred by Michael 08:38.550 --> 08:42.690 and the memoir referred by George and as referred by George. 08:42.840 --> 08:45.660 So this is a way you can actually do that. 08:45.660 --> 08:51.900 And, uh, we have done the example of a self join in the previous lecture, and we also did it right 08:51.990 --> 08:52.200 here. 08:52.230 --> 08:58.410 So you can actually use that to match your affiliates or supervisors or any other thing in the industry. 08:58.410 --> 09:00.540 So it can be applied in any way. 09:00.570 --> 09:05.550 So why not apply that in any format or any form you want. 09:05.550 --> 09:08.070 And it's going to work out fine for in this process. 09:08.070 --> 09:11.640 So thank you so much and I'm going to see you in the next video lecture.