WEBVTT

00:07.520 --> 00:10.220
I will call you back again to lecture in MySQL.

00:10.250 --> 00:14.750
And in this lecture let's go ahead and look at Self-join in MySQL.

00:15.230 --> 00:15.530
Alright.

00:15.530 --> 00:20.540
So self-join is used to compare a table to itself.

00:20.540 --> 00:23.030
It means that we have the students table right in here.

00:23.060 --> 00:28.760
We can go ahead and compare this students table to itself by combining this table to itself.

00:28.760 --> 00:33.620
So we can be able to display the hierarchy of data in a table using Self-join.

00:33.740 --> 00:36.500
And then we compare the rows of the table.

00:36.500 --> 00:41.060
So now I know as a beginner you might be like, what am I talking about?

00:41.060 --> 00:42.590
I'm going to do that with an example.

00:42.590 --> 00:48.260
So right in here I created a supervisor column because I want to be showing this with an example.

00:48.260 --> 00:53.510
And I use this query right in here to create this, uh, supervisor column.

00:53.510 --> 00:57.770
So go ahead and create this column right in here by adding this to your query.

00:57.770 --> 01:01.020
And that will be inserted into your students table.

01:01.410 --> 01:09.060
Now, what I did writing here is that I want my students to be supervised by each other.

01:09.090 --> 01:16.350
Now, what happens is I'm going to I created this for my source so that every student writing here will

01:16.350 --> 01:20.100
be reporting to Queen except beta.

01:20.130 --> 01:22.500
Beta is going to be the main supervisor here.

01:22.830 --> 01:23.520
Let me say this.

01:23.520 --> 01:24.750
Be more like a prefect.

01:24.780 --> 01:27.690
So Queen is like a prefect, but he she.

01:27.720 --> 01:29.250
It will be called a supervisor.

01:29.280 --> 01:35.040
So Oscar, barracks, Zara, Mike and Zenda will be reporting to Queen.

01:35.040 --> 01:37.620
And then Queen will be reporting to Peter.

01:37.650 --> 01:40.050
Therefore, Peter is the main supervisor.

01:40.110 --> 01:47.250
So I'm going to assign the IDs of Oscar, Barak, Sarah, Mike and Zenda to Queen.

01:47.280 --> 01:47.880
Why?

01:47.910 --> 01:53.280
Assign Queen students ID to Peter, whom Peter Queen will be reporting to.

01:53.310 --> 01:56.190
So we're not going to assign one supervisor ID.

01:56.880 --> 01:58.770
Peter is going to be empty.

01:59.380 --> 02:00.040
Why?

02:00.070 --> 02:02.560
Queen will be assigned to Peter.

02:03.160 --> 02:06.400
All right, so let me go ahead and show you how this actually works.

02:06.400 --> 02:09.190
So let me go ahead and remove everything right in here.

02:09.220 --> 02:12.670
So that's how every students are going to have a supervisor.

02:12.670 --> 02:17.950
And what I need to do is I will go ahead on the updates, the student table.

02:17.950 --> 02:20.290
So go ahead and say update students.

02:20.290 --> 02:26.320
And now go ahead and set supervisor.

02:27.790 --> 02:34.600
So that is our supervisor underscore ID and uh let this be equal to I'm going to leave it to pull right

02:34.600 --> 02:35.170
in here.

02:35.170 --> 02:38.500
And uh let me go ahead and say where.

02:40.570 --> 02:44.410
Students underscore ID is equal to semicolon.

02:44.920 --> 02:45.280
All right.

02:45.310 --> 02:51.550
Now what actually happens here is that this supervisor ID, which I'm going to set is going to be that

02:51.550 --> 02:52.690
of a queen.

02:52.690 --> 02:54.280
So I'm going to say queen.

02:54.280 --> 03:03.600
So set supervisor ID where which is equal to six, where the stress ID Oscar, Barak, Sarah, Mike

03:03.600 --> 03:07.410
and Zander we pointed to were assigned to Quinn.

03:07.410 --> 03:11.220
So I'd say this ID is for Oscar.

03:11.250 --> 03:13.140
Go ahead and execute this query.

03:13.290 --> 03:21.210
Check it out so you can see that I've assigned the ID of Queen to as a supervisor to Oscar.

03:21.210 --> 03:28.050
And now we will go ahead and do that for all this and J dot again.

03:29.460 --> 03:32.760
And there is the next one.

03:32.760 --> 03:35.130
So the next one is a fifth.

03:36.180 --> 03:38.190
And the for queen.

03:38.190 --> 03:42.030
What I'm going to do down here I'll go ahead and jump this to seven.

03:42.060 --> 03:45.120
Then I'll go ahead and you see Queen is no.

03:45.120 --> 03:50.370
Now what I'm going to do now is provide the ID of Queen is going to be one where the stress ID is equal

03:50.370 --> 03:51.090
to six.

03:51.090 --> 03:52.800
And let's go ahead and check it out.

03:53.460 --> 03:54.420
So.

03:56.830 --> 04:05.680
Now you can see that I've assigned this supervisor ID of Peter to Queen, and Peter is not assigned

04:05.710 --> 04:06.880
to anybody.

04:07.210 --> 04:09.820
Okay, you might be like, wow, Peter is no.

04:09.820 --> 04:16.240
How can we be able to dictate that Peter is actually a queen is actually assigned to Peter?

04:16.450 --> 04:21.580
So and how do we know that Mike is assigned to Queen?

04:21.610 --> 04:24.310
Now let's go ahead and use a join to do that.

04:24.340 --> 04:27.400
Go ahead and remove this over here and remove everything right in here.

04:27.400 --> 04:35.320
And then what I need to do is just go ahead and say select all from.

04:35.650 --> 04:38.950
Then I'm going to say students.

04:41.410 --> 04:47.800
Students as an assign an alias to this E then I'm going to use the inner join.

04:47.830 --> 04:55.090
So inner join students as B.

04:55.120 --> 04:56.300
Now unstress us.

04:56.330 --> 04:58.190
A is for the stress ID.

04:58.220 --> 04:59.330
Why stress us?

04:59.360 --> 04:59.720
B?

05:00.110 --> 05:02.900
Oh, this is stress as B is for the supervisor.

05:02.930 --> 05:03.290
ID.

05:03.530 --> 05:04.160
All right.

05:04.160 --> 05:05.780
So that's all I want to do right in here.

05:05.780 --> 05:08.840
And our head and our do that.

05:08.840 --> 05:22.490
So so we can be able to see that on then a dot go ahead and say a dot uh supervisor underscore ID.

05:22.490 --> 05:25.460
So stress A is for supervisor.

05:25.460 --> 05:27.680
So I hope that there was a mistake before.

05:27.710 --> 05:29.270
So stress A is for supervisor.

05:29.300 --> 05:29.720
Why.

05:29.750 --> 05:32.300
Stress B is for stress ID right.

05:32.330 --> 05:34.310
Which is equal to.

05:35.540 --> 05:42.980
Let's go ahead and say this is equal to B dot students.

05:44.000 --> 05:44.300
Right.

05:44.300 --> 05:45.770
So stress ID.

05:45.950 --> 05:50.180
Now let's go ahead and uh run this and check it out.

05:50.300 --> 05:51.350
Go ahead and execute.

05:51.380 --> 05:51.590
Okay.

05:51.590 --> 05:54.350
Put a semicolon at the end of this so you don't run into problems.

05:54.350 --> 05:54.390
Problem.

05:54.390 --> 05:56.700
And they also show this right.

05:56.700 --> 05:58.740
So go ahead and remove this because it's.

05:58.770 --> 05:59.820
Actually showing the table.

05:59.850 --> 06:00.570
Go ahead and remove this.

06:00.570 --> 06:02.220
So can be able to see what we.

06:02.250 --> 06:03.600
Just selected which is this.

06:03.630 --> 06:05.040
Go ahead and execute this again.

06:05.040 --> 06:07.620
And now you can still have a very large table.

06:07.650 --> 06:08.460
Right in here.

06:08.490 --> 06:10.440
Well that is very very long.

06:10.440 --> 06:17.280
And uh you can go ahead and reduce this for your font so you can go over here and uh, check out this,

06:17.280 --> 06:19.230
reduce this font, okay.

06:19.230 --> 06:22.020
Because that is one of the reasons why that is very big.

06:22.020 --> 06:28.650
But I want this to conclude so that, uh, we're going to actually remove the things we don't need.

06:28.650 --> 06:30.510
Like, I don't want the raw date.

06:30.510 --> 06:35.580
I don't want the department, I don't want the GP, I don't want the supervisor ID and the test ID right

06:35.580 --> 06:36.090
in here.

06:36.090 --> 06:39.570
And, uh, I don't want all this to appear twice right in the air.

06:39.600 --> 06:41.160
What if you can look over here?

06:41.160 --> 06:44.730
You can see Queen is assigned to this guy's queen.

06:44.730 --> 06:44.970
Queen?

06:44.970 --> 06:45.330
Queen.

06:45.330 --> 06:47.400
And, uh, Peter right here.

06:47.430 --> 06:49.740
Now, all I'm going to do is this.

06:49.740 --> 06:55.150
Go ahead and select just what we need so that this table can be organized because we have a very large

06:55.180 --> 06:55.930
table right in here.

06:55.930 --> 07:00.880
So what I'm going to do is select them all, go ahead and reduce this.

07:00.880 --> 07:04.210
So you can actually go ahead and put this in one single line.

07:04.210 --> 07:06.370
I'm going to name the tables and need.

07:06.370 --> 07:13.780
And if I should go ahead and name the tables I need I'll go ahead and say I need a dot first name.

07:13.810 --> 07:15.160
All right comma.

07:15.160 --> 07:21.640
And I need a dot last name comma.

07:21.640 --> 07:29.380
And I need b dot first name and B dot last name.

07:29.410 --> 07:31.930
Now let's go ahead and execute this query and check it out.

07:32.050 --> 07:32.620
All right.

07:32.620 --> 07:35.200
So the first name of this terrain is Queen.

07:35.440 --> 07:41.890
And you can see that we are using a Join Queens which is assigned to Peter Jude.

07:41.890 --> 07:48.670
And then every order of the students we are assigned to Queens meet can see that.

07:48.670 --> 07:53.700
And uh, actually it might not look good to you because I see first name, first name, last name,

07:53.700 --> 07:54.300
last name.

07:54.300 --> 07:57.000
How can we be able to actually.

07:57.030 --> 07:57.480
Okay.

07:57.510 --> 08:05.400
Let me say let's go ahead and say we want this first name and last name to be like project manager okay.

08:05.430 --> 08:08.460
We want this first name and last name to be project manager.

08:08.490 --> 08:10.500
I will give it a alias like supervisor.

08:10.980 --> 08:11.190
Okay.

08:11.190 --> 08:13.110
Let's go ahead and say it is a supervisor.

08:13.140 --> 08:20.040
What I'm going to do, um, right in here is that, uh, you can actually concatenate these and go ahead

08:20.040 --> 08:21.540
and bring this down.

08:21.540 --> 08:24.750
I just want this to look good concat.

08:24.750 --> 08:28.560
And then go ahead and close this as well.

08:28.560 --> 08:30.510
And I can name this to say.

08:30.660 --> 08:36.150
And I will name this to be supervisor.

08:37.350 --> 08:40.200
All right, all right, all right.

08:40.200 --> 08:41.370
And then this ratio.

08:41.370 --> 08:46.230
And here I will go ahead and uh leave this.

08:46.230 --> 08:49.710
So what is concat alias as supervisor.

08:49.830 --> 08:55.060
It should go to the place the name first name and last name to be supervisor.

08:55.090 --> 09:00.700
Then you're going to have just the student's name, which is a first name, last name for the students.

09:00.700 --> 09:06.340
And then this guy is going to change, but first name and last name are going to change to supervisor

09:06.430 --> 09:07.150
while under.

09:07.180 --> 09:07.900
Check this out.

09:07.930 --> 09:08.620
Let's see it.

09:08.620 --> 09:15.610
So now you can see we have our supervisor and we have Peter George and our Chris Smith joined together.

09:15.640 --> 09:18.400
What I'm going to do is I'm going to give a space right in there.

09:18.400 --> 09:26.230
So go right in here and give a space and put a space right in here and check this out.

09:26.230 --> 09:29.560
So Peter Jud supervisor.

09:29.560 --> 09:36.880
And you might be like, okay, we have these students listed, but Peter is not actually listed as a

09:36.880 --> 09:37.330
student.

09:37.360 --> 09:37.720
Yes.

09:37.720 --> 09:39.790
Peter is not reporting to anybody.

09:39.790 --> 09:41.140
But how do we know that?

09:41.170 --> 09:44.650
Go ahead and change this in a joint left joint.

09:44.650 --> 09:48.700
So change it in a joint to left joint and check it out.

09:49.360 --> 09:50.260
You can see that.

09:50.260 --> 09:50.270
date.

09:50.300 --> 09:54.080
Peter Jud is the supervisor now reporting to anybody.

09:54.140 --> 09:58.880
And the Oscar show reporting to Smith Barracks.

09:58.880 --> 10:00.650
Harry reporting to Chris Smith.

10:00.680 --> 10:06.440
Sarah Rockey reporting to Chris Smith and Mike Samson reporting to Chris Smith.

10:06.470 --> 10:13.970
Then Troy Smith is reporting to Peter Jud and Zander grass is also reporting to Quinn Smith.

10:14.270 --> 10:15.470
Oh, that is cool.

10:15.500 --> 10:20.120
So in the next lecture I'll go ahead and make one more example so you can be able to see how good it

10:20.120 --> 10:20.600
looks.

10:20.630 --> 10:21.890
I hope that is very beautiful.

10:21.890 --> 10:23.180
So check it out.

10:23.180 --> 10:27.830
And I'm going to bring this down a little bit so I can be able to see how the query right in here.

10:27.830 --> 10:29.750
So is this in practice.

10:29.750 --> 10:33.260
And if you have any questions please go ahead and use the question and answer section.

10:33.260 --> 10:35.750
And I'm going to get back to you as soon as possible.

10:35.780 --> 10:36.740
Thank you so much.

10:36.740 --> 10:40.370
And I'm going to see you in the next video lecture.