I will call you back again to lecture in MySQL.
And in this lecture let's go ahead and look at Self-join in MySQL.
Alright.
So self-join is used to compare a table to itself.
It means that we have the students table right in here.
We can go ahead and compare this students table to itself by combining this table to itself.
So we can be able to display the hierarchy of data in a table using Self-join.
And then we compare the rows of the table.
So now I know as a beginner you might be like, what am I talking about?
I'm going to do that with an example.
So right in here I created a supervisor column because I want to be showing this with an example.
And I use this query right in here to create this, uh, supervisor column.
So go ahead and create this column right in here by adding this to your query.
And that will be inserted into your students table.
Now, what I did writing here is that I want my students to be supervised by each other.
Now, what happens is I'm going to I created this for my source so that every student writing here will
be reporting to Queen except beta.
Beta is going to be the main supervisor here.
Let me say this.
Be more like a prefect.
So Queen is like a prefect, but he she.
It will be called a supervisor.
So Oscar, barracks, Zara, Mike and Zenda will be reporting to Queen.
And then Queen will be reporting to Peter.
Therefore, Peter is the main supervisor.
So I'm going to assign the IDs of Oscar, Barak, Sarah, Mike and Zenda to Queen.
Why?
Assign Queen students ID to Peter, whom Peter Queen will be reporting to.
So we're not going to assign one supervisor ID.
Peter is going to be empty.
Why?
Queen will be assigned to Peter.
All right, so let me go ahead and show you how this actually works.
So let me go ahead and remove everything right in here.
So that's how every students are going to have a supervisor.
And what I need to do is I will go ahead on the updates, the student table.
So go ahead and say update students.
And now go ahead and set supervisor.
So that is our supervisor underscore ID and uh let this be equal to I'm going to leave it to pull right
in here.
And uh let me go ahead and say where.
Students underscore ID is equal to semicolon.
All right.
Now what actually happens here is that this supervisor ID, which I'm going to set is going to be that
of a queen.
So I'm going to say queen.
So set supervisor ID where which is equal to six, where the stress ID Oscar, Barak, Sarah, Mike
and Zander we pointed to were assigned to Quinn.
So I'd say this ID is for Oscar.
Go ahead and execute this query.
Check it out so you can see that I've assigned the ID of Queen to as a supervisor to Oscar.
And now we will go ahead and do that for all this and J dot again.
And there is the next one.
So the next one is a fifth.
And the for queen.
What I'm going to do down here I'll go ahead and jump this to seven.
Then I'll go ahead and you see Queen is no.
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
to six.
And let's go ahead and check it out.
So.
Now you can see that I've assigned this supervisor ID of Peter to Queen, and Peter is not assigned
to anybody.
Okay, you might be like, wow, Peter is no.
How can we be able to dictate that Peter is actually a queen is actually assigned to Peter?
So and how do we know that Mike is assigned to Queen?
Now let's go ahead and use a join to do that.
Go ahead and remove this over here and remove everything right in here.
And then what I need to do is just go ahead and say select all from.
Then I'm going to say students.
Students as an assign an alias to this E then I'm going to use the inner join.
So inner join students as B.
Now unstress us.
A is for the stress ID.
Why stress us?
B?
Oh, this is stress as B is for the supervisor.
ID.
All right.
So that's all I want to do right in here.
And our head and our do that.
So so we can be able to see that on then a dot go ahead and say a dot uh supervisor underscore ID.
So stress A is for supervisor.
So I hope that there was a mistake before.
So stress A is for supervisor.
Why.
Stress B is for stress ID right.
Which is equal to.
Let's go ahead and say this is equal to B dot students.
Right.
So stress ID.
Now let's go ahead and uh run this and check it out.
Go ahead and execute.
Okay.
Put a semicolon at the end of this so you don't run into problems.
Problem.
And they also show this right.
So go ahead and remove this because it's.
Actually showing the table.
Go ahead and remove this.
So can be able to see what we.
Just selected which is this.
Go ahead and execute this again.
And now you can still have a very large table.
Right in here.
Well that is very very long.
And uh you can go ahead and reduce this for your font so you can go over here and uh, check out this,
reduce this font, okay.
Because that is one of the reasons why that is very big.
But I want this to conclude so that, uh, we're going to actually remove the things we don't need.
Like, I don't want the raw date.
I don't want the department, I don't want the GP, I don't want the supervisor ID and the test ID right
in here.
And, uh, I don't want all this to appear twice right in the air.
What if you can look over here?
You can see Queen is assigned to this guy's queen.
Queen?
Queen.
And, uh, Peter right here.
Now, all I'm going to do is this.
Go ahead and select just what we need so that this table can be organized because we have a very large
table right in here.
So what I'm going to do is select them all, go ahead and reduce this.
So you can actually go ahead and put this in one single line.
I'm going to name the tables and need.
And if I should go ahead and name the tables I need I'll go ahead and say I need a dot first name.
All right comma.
And I need a dot last name comma.
And I need b dot first name and B dot last name.
Now let's go ahead and execute this query and check it out.
All right.
So the first name of this terrain is Queen.
And you can see that we are using a Join Queens which is assigned to Peter Jude.
And then every order of the students we are assigned to Queens meet can see that.
And uh, actually it might not look good to you because I see first name, first name, last name,
last name.
How can we be able to actually.
Okay.
Let me say let's go ahead and say we want this first name and last name to be like project manager okay.
We want this first name and last name to be project manager.
I will give it a alias like supervisor.
Okay.
Let's go ahead and say it is a supervisor.
What I'm going to do, um, right in here is that, uh, you can actually concatenate these and go ahead
and bring this down.
I just want this to look good concat.
And then go ahead and close this as well.
And I can name this to say.
And I will name this to be supervisor.
All right, all right, all right.
And then this ratio.
And here I will go ahead and uh leave this.
So what is concat alias as supervisor.
It should go to the place the name first name and last name to be supervisor.
Then you're going to have just the student's name, which is a first name, last name for the students.
And then this guy is going to change, but first name and last name are going to change to supervisor
while under.
Check this out.
Let's see it.
So now you can see we have our supervisor and we have Peter George and our Chris Smith joined together.
What I'm going to do is I'm going to give a space right in there.
So go right in here and give a space and put a space right in here and check this out.
So Peter Jud supervisor.
And you might be like, okay, we have these students listed, but Peter is not actually listed as a
student.
Yes.
Peter is not reporting to anybody.
But how do we know that?
Go ahead and change this in a joint left joint.
So change it in a joint to left joint and check it out.
You can see that.
date.
Peter Jud is the supervisor now reporting to anybody.
And the Oscar show reporting to Smith Barracks.
Harry reporting to Chris Smith.
Sarah Rockey reporting to Chris Smith and Mike Samson reporting to Chris Smith.
Then Troy Smith is reporting to Peter Jud and Zander grass is also reporting to Quinn Smith.
Oh, that is cool.
So in the next lecture I'll go ahead and make one more example so you can be able to see how good it
looks.
I hope that is very beautiful.
So check it out.
And I'm going to bring this down a little bit so I can be able to see how the query right in here.
So is this in practice.
And if you have any questions please go ahead and use the question and answer section.
And I'm going to get back to you as soon as possible.
Thank you so much.
And I'm going to see you in the next video lecture.