I will call you back again to another video lecture on MySQL.
And in this video lecture, let's go ahead and look at another example of Self-join in MySQL.
Our previous lecture, we were able to look at the students table right in here before us.
We have a table called the employees table and other employees towards that table we have created in
the previous lectures.
Then I went ahead and added affiliate ID, and I just used this code already here to create that.
So go ahead and put that down.
Alright, so within our self-join is used to display the hierarchy of data in our tables.
And we also use the Self-join to actually join a copy of table to itself.
And then go ahead and compare the rules.
So now I have this employee table one here, and I added affiliate.
And what I want to display here is that I want each of these employees from Kizito Messi, George Chief
and MLA to be referred Refreshed by Michael.
So I want Michael to be the one referring them.
So Michael is referring everybody.
And then the affiliate IDs are going to be that of Michael and the Michael employee ID is one.
So whenever the athlete ID is equal to 2 or 3, four, five, we'll go ahead and put the.
So we're going to put Michael employee ID there.
So if we have employee ID equal to two then the affiliate ID is going to be equal to one.
All right.
So in that case that is where we're going to do that.
Then let's go ahead and remove all these.
Then we go ahead and update the employees affiliate column.
And now let's go ahead and say update.
And then we'll go ahead and say employees.
And then we go ahead and set affiliates.
So set affiliates underscore ID is equal to one because we are using the athlete ID of Michael, then
we go ahead and say, isn't a VR class where the employee.
Oh, that should be a lower case where the employee underscore ID is equal to two and so on.
So we're going to order 2345678.
And so now let's go ahead and update the very first one and check it out.
So that is one.
So the athlete ID is a reflection of the Michael.
Then we'll go ahead and see.
This is a three overhead and that is a one.
And the four go ahead and execute that.
And that is one again.
Now what if we say that George actually referred to Steve Case.
Let's go ahead and say that Michael referred George referred to Messi and George and George went ahead
and referred Steve.
If we want to do that, we go ahead and set where the athlete ID is equal to four.
then the employee ID is equal to five.
This is so you can see that the employee ID for judge is now displayed as an athlete ID for Steve.
Okay.
And um, we want to say maybe judge also ordered a memoir.
So go ahead and change this employee ID and let's check it out.
So this is amazing waterfront by Michael I'm the judge, not Steve.
And the memoir.
And now we want to join this table.
Let's go ahead and join this table using the inner join.
So we can be able to actually match all these, uh, people with the people that actually, uh, refer
them.
So what we're going to do is go ahead and uh, select white and select uh oh from we're going to select
all from our employees.
All right.
So we're going to give an alias.
We are alias a employee as a.
So we're going to the same place as a and, uh, in a joint.
So when we use the inner join that we did in the previous lecture and uh c employee.
We're using the alias SB.
Now let's go ahead and uh match these same all in dot affiliates.
And dot affiliates underscore ID somehow I'm not going to make the column the affiliate column with
the employee column.
So affiliate ID equal to B dot uh employees underscore ID put a semicolon at the end of this.
So now go ahead and execute this query.
Let's ensure okay I have this always right in a because I don't always remove this.
So it's always going to display.
Go ahead and take it out.
nuts and let's see what goes on.
So from this error we set out our table.
Employee doesn't exist.
So what we have is employees and not employee right.
So go ahead and execute this query again because we missed the s right right in there.
And now we have right in here um the athlete is in Michael Michael Werner if anybody.
And that is why you can see my athlete's ID for Michael is no.
But athlete ID for George is a one and one because George was referred by Michael and Michael actually
referring George, and Michael is referring Messi and Kizito while George referring and memoir.
And Steve, uh, because you're using inner join.
That is why this is, uh, in ascending order.
If you go ahead and use left, join the square hat and use a left join and check it out so you can see
that our is now displayed according to the IDs and are no longer alphabetical order.
All right.
So if you use the inner join is going to be in a ascending order or alphabetical order.
What if you use left Join its core head and check that out by ID?
And that is what we have right in here.
Now let's go ahead and display only the specific table that we need because we are selecting everything.
And I want the athlete ID and the employee ID are these to be shown at the KPA.
I just want the first name of the employees and the first name of the program that referred them to
match.
So what I'm going to do is that I'm going to remove this select all, and I'm going to say selector
a dot uh, select dot employee or underscore ID.
And um then I'm going to say a dot first underscore name and uh b dot uh first underscore name.
Alright.
Now I'll go ahead and execute this query again.
Let's check it out.
So now I can see that my code was actually referred by nobody.
And that is why we have no why.
Because it was referred by Michael Messi.
Referred by Michael George followed by Michael and I'll Still Refer by George and the memoir referred
by George.
So that is what we have.
But now we can see we have our first name, first name, and now how can we be able to remove these
so that we can just have a referral or affiliate.
So we can just make this to be referral.
So how can we actually do that.
So what do we need to do right here is are we going to concatenate the very first what we have here.
So what I'm going to do is I where I say concat and then I go ahead and close this.
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
extending to the next line.
And I can actually go ahead and bring this one up down as well.
There is no problem.
It's uh for clarity purpose.
Then I go ahead and make this an alias.
I'm going to say S then I can refer them this as a referral.
All right.
So I hope that is cool.
Now if we go ahead and, uh, run this, this query.
So now this is our employee ID, the first name of the employees and the referrers.
So this is not a referrers.
And now if I go ahead and continue to use an inner join.
So use inner join.
And it's going to remove my code up there.
And you can see that judge the four by Michael Messi referred by Michael because it's referred by Michael
and the memoir referred by George and as referred by George.
So this is a way you can actually do that.
And, uh, we have done the example of a self join in the previous lecture, and we also did it right
here.
So you can actually use that to match your affiliates or supervisors or any other thing in the industry.
So it can be applied in any way.
So why not apply that in any format or any form you want.
And it's going to work out fine for in this process.
So thank you so much and I'm going to see you in the next video lecture.