I will call you back again to another lecture in PostgreSQL.
Let's go ahead and look at joining PostgreSQL.
So in the previous lecture we have these tables.
And we're able to assign some fields to some of our students.
And let's go and assign select our from our students.
And then we have this phone assigned to these students.
And let's go ahead and select the next table and select our all from phone.
And we have these phones assigned to these students.
Now I know that you as a beginner in PostgreSQL or in SQL might be like, wow, I want to see maybe
first name Rosalind, George, Lulu and Tubby to be able to have this phone Motorola I made and Samsung
assigned and not just their ID A.D. and that is going to make more sense to us as beginners.
Yes, I also agree with you because that is how it's supposed to be.
I don't just have to assign only the IDs which has seen this.
Now I'm going to assign the first name, and I'm going to join that with the brand of this phone and
then with the model.
Or we just go ahead and assign that with the brand and the price, because the model is still on the
same thing with the brand.
We have Motorola and Motorola moto E play.
We don't just need this.
So let's go ahead and say Russell.
And then the next column is going to be the brand of the phone assigned to a resident or judge.
And then the price of the phone that the student is using.
So this is where the join actually comes together.
So the join helps us to join two tables.
And then we go ahead and have just one particular table.
So we join the table A and table B and they will create a table C.
And that is where you can be able to see each of the students with a first name or the last name.
And you anyone you choose.
We can actually do the first and last name, and then we have the brand of the phone and the prices.
And you can also do the first name and the brand of the phone and loan anyone.
I'm going to show you how to do that.
So let's go ahead and get started immediately.
So let's go ahead and see for us to train that I'm going to select AU from the students table.
So go ahead and select AU from students.
And I'm not going to press enter right in here.
And I'm going to put the semicolon at the end.
So go ahead and say join.
Alright.
So I'm going to use an uppercase because that's a keyword.
So join.
Then I'm going to join the phone.
All right.
So I'm going to join the table phone I'm going to say join phone.
And uh I'm going to use the on keyword.
Now the on keyword we have asked to create a new table.
And this table is going to be the student table and the phone table.
And if we want to do that, what are we going to do is we're going to create a new table within the
students table, including include the font ID and the the font table.
Include the font ID uh, as well.
So the font ID is this.
And uh for the stress table, this is for the font ID we're going to use.
So let me go ahead and say students dot.
Then go ahead and say font underscore ID phone underscore ID is equal to then phone dot.
Then this is the ID I'm going to match that with that idea.
Now I'm go ahead and put this uh semicolon at the end and hit enter.
And now I'm going to expand this.
All right.
Go ahead and expand this.
And now you can see that I have match these two tables.
I've mentioned this to tables.
And uh you can see what I have right in here.
I have this table of our students and this table of phone in one new table.
Check it out.
The phone.
The student level is this.
And the phone table is this.
And I hope that is cool.
Now that one looks really so much long and you may be like, whoa, this is not really looking good.
What do we need now is how can we just take only the name of the student?
Let me say, uh, Lulu, Toby and George.
But before we do that, check out the students we have here.
We have four of the students, and we have one of the student, which is our Russian land or Mayweather.
No phone was assigned to this student and we can check on the new table.
Would not have Rosalind or Mayweather here because no phone was assigned to him or her.
That is a hair.
All right.
So all right, is she.
So no phone was assigned to her, and, uh, only the people who have phones assigned to them.
We are listed out.
So in this way, you can see that the foreign key actually works.
And we can also join this table.
Now I'm going to trim this table so we can just have the first and the last name and then the brand
of the phone and the price of the phone that we assigned to them.
There.while I show you how to do that.
So I'll go right in here.
And all I need to do is go ahead and specify the columns I want.
So go ahead and say select a no longer all, but I'll go ahead and say select a students dot first underscore
name and put a comma.
If I want to select the last name two I'll go ahead and say students dot last underscore name and put
a comma because I need these two rows okay.
You can also specify just one because I just needed two.
No problem.
And then I'll go ahead and go over the phone and say phone dots.
I need a brand of the phone phone brand and put a comma and then phone dot price.
All right.
I don't need the model because this model is too long and it tells the specification of the same brand.
So there is no need putting that there.
And then I'll go ahead and hit enter without a semicolon.
Then I'm going to select that from the students.
Go ahead and select that from the students table.
And I'll go ahead and join that with the phone table.
And I'm going to say phone.
And uh using the same all to create a new table.
Go ahead and use the all keyword to create a new table.
And that table is a table of uh, students dot phone underscore ID equal to
phone.id.
Then put a semicolon at the end of this.
And then we go ahead and hit enter.
And now we can see we have first name rural.
And that is the last name brand phone Motorola.
And the price is DS.
And Toby Acosta has a brand of image.
And this is a price of the phone.
And George Steve has a brand of Samsung with a price of this Samsung phone.
So in that way you can be able to join two tables using the join keyword and you can see the step by
step.
And then it looks so beautiful.
Go ahead and play around with these.
Check it out.
And if you have any question you go ahead and use the question and answer section.
And I'm going to see you in the next video lecture.
So remember that this is the part of the foreign key.
Thank you and see you in the next video lecture.