Is that cost money?

If you appreciate this education, please think about going to Eli the computer guy dot com and offering a one time or monthly recurring donation.

Welcome back.

As you know, I am Eli in a computer guy.

In today's class, we're going to be talking about joins or, more specifically, inner joins in regards to my sequel databases.

So when we're talking about joins, what we're talking about doing is actually taking two different tables within your my sequel database and connecting them and being able to deal with them as if they were one single table.

So why this is important is remember, my sequel is a relational database.

So what this means is you have numerous different tables within your database, and each one of these tables is but supposed to have a specific type of information to make it easier to deal with from a logical standpoint.

So you may have a parts table and you may have a vendor's table, and you may have an order's table, and you may have a customer's table right.

You have a lot of different tables.

Those tables are specific to one that specific type of data that you're trying to bring in.

And then what joins allow you to do is joins, allow you to connect two different tables together and then be able to run sequel statements off of those two tables as if they were where they were.

One single table.

So what I'm gonna be showing you today is we're going to be using an example using something called Inner Join, and we're going to take the parts table So the parts table has a part saidi and has a parts name.

It has a parts price, and then it has a connection.

It has a vendor's I D right.

So instead of actually putting all the information for the vendors within the parts table, you're going to have in a vendor's I D.

And then you're going to have a vendor's table, and that vendor's table is going to have a vendor's I D.

And it's going to have a vendor's name and is going to the vendors address, and it can have a whole bunch of other things there.

And so what we're going to do is we're going to join the two tables together, and then you can run a select statement off of those two tables as if there are one single table.

So basically, you can know that cause eggs come from Suze Cox Company in California, right?

And so basically, you're combining those two tables so that you can then run statements off of them as if they were one single table.

And so this is one of things that makes my sequel very valuable and very functional.

And this is how you start bringing those different tables together.

That's when the issues a lot of new people have.

When they come to my sequel and you see the different tables, right, you get the concept.

You get the concept of okay.

Users table on an invoice table imparts table vendor's table.

But then the question is as well.

But then how do you connect those tables together in order to make something that's actually useful?

And one of the ways that you're able to do that is you're able to do that with these joins.

So again, like with Oh, let's say with it with an invoice, right?

So with an invoice table, you could join, and so you could just you could reference a customer idea, having all the customer information went in that that that voice table, you could reference simply a customer, I d.

And then you could reference simply parts, ideas or things like that.

And that way you're one invoice table can can remain relatively small and doesn't actually have a have to have a lot of information in itself because it's referencing these other tables.

And then you're able to do joins than to make when you're looking at views or whatever.

Basically, you're able to going these multiple tables into one single table in order to be able to interact with.

So, as I've said many times in a serious warning, a warning Will Robinson warning, Warning.

That's when we start talking about joins.

There are actually numerous the different types of joins.

There's the inner join that we're gonna be talking about today.

There's right joins.

There's left joins, right?

There are numerous different types of joins in these different joins give you different results.

One of the problems you run into, especially with new people, though, is you start talking about all these different types of joins and then people were confused and frustrated.

Then they go off to be florists, right?

So I'm just going to be explaining to you and inner join today.

And then in a future class, we may talk about the different types of joints or, once you understand, inter joins, you can go out and you could do some Google Google searching on your own toe.

Figure out the value of Wright joins and left joints and that type of thing.

The important thing to understand about Inter joins we're doing an inner joint is you're taking two tables and basically you're joining those two tables off of columns that are both fully populated, right?

So today, when we're when we're going to be doing the joints, we might have a parts table, and we're going have a vendor's table within the parts table.

There is going to be a vendor underscore idea column, right?

And so for all of the parts that will be populated, they will all have either be vendor I d.

One vendor i d.

Two or vendor I D three and then over in the vendor's table.

The vendor's table will also have a vendor underscore i d column and that obviously will be populated.

And so, basically, since these two tables have the vendor I D column populated, then you will be able to combine them with an inner join.

If for some reason, not all the fields when a column are fully populated, that's when you would want to do something such as a left joint, right join.

But we may talk about those in the future again.

Do your research.

So with this with this, the idea is is the columns are fully populated.

So every record in the column for Vendor underscore I.

D.

It has something in that field and then that is able to correspond to the vendor I d over in the vendor field.

So that's just something to keep in mind with what we're doing today.

Uh, just you know, when you when you're going ahead, do you realize that there are other different types of joints out there.

So if you're looking for a different way to be able to connect two tables, you might want to take a look at left joints or right joins.

So now you're starting to have an idea of what a joint is let's actually go over the whiteboard.

So Aikens kind of scribble out what we're going to be doing before we go to the computer and I actually demonstrate for you today.

Okay, so here we are at the white board, and so we're gonna be dealing with two different tables.

The first table we will be dealing with is the parts table.

Right.

So we have a parts table within the parts table.

We're going to have a party, I d.

We're going to have a part name.

We're going to have a part price.

And then the important one is we're going to have a vendor, I d right.

And so those are there going to be?

The columns that we will be dealing with within those columns will be numerous different records.

Then we're going to be having the vendor's table and in the vendor's table again, there will be multiple different columns.

The first column will be the vendor, i d.

And then there will be the vendor name.

And then they will be the vendor address, right?

And then there will be different records here.

And so basically, what we're able to do is were then able to populate the parts table.

And so there will be a part i d.

One.

The name will be something like Cog.

The price will be, let's say, $1 then we can do is we can say, a vendor i d of one.

Then we can have a different parts apart I d to.

That is a nut price might be, too, and that has a vendor idea of two.

I, uh, part I.

D.

Of three.

That's some kind of widget price of three.

A vendor idea of three, right?

And then you come over here to the vendor's table and in the vendor's table we have the vendor i D.

12 and three, and then you'll have, you know, some company name under the Name Inc LLC Corp.

And then you'll have the address.

And essentially, what will be able to do is using the join.

What we're going to do is we're going to join based off of this vendor i D number so that at the end of the day, as far as our sequel statements are gonna be concerned, we're going tohave one table.

So we're gonna have the parts table joined to the vendor table, and then here it will have the part i d.

Another part name, the part Price.

Then I'll have the vendor i d.

And then for the vendor.

It'll have a vendor, I d.

Then I'll have the name and then I'll have the address.

And so basically, what will be able to do is we'll be able to selects and all other kinds of, like, my sequel statements because we're now going to be joining based off of that vendor i d.

Now it is important to understand, with the inner join the type of joint that I'm showing you today that this vendor I D has to be populated on both sides.

So so 11223344 and so on.

If for some reason there isn't a vendor i d in one of one of the records, you will have problems doing an inter join.

But if you have a vendor I d in in both in both columns and both records in both tables.

Then you'll be able to join, and then you'll be able to interact with these two tables as if there are one single table.

So with that, let's go over the computer so I can actually show you how this works within my sequel.

Okay, so here we are, back at my lab machine again.

I've got a bunch of desktop 18.4 It is installed with an instance of workable box that is on my Mac book pro.

I have used task Cell to install their full lamp stack.

So we have a packing my sequel and PHP on this particular system, and we're gonna go down to the lower left hand corner, click on a show applications and then up here, we're gonna type and a terminal so we could get through the terminal.

And yet we now have terminal.

Now, in order to log into my sequel, you should now have a user account.

So in a previous class, I show you how to create my sequel user accounts.

If you do not have a user account yet for some reason, you could you soon.

Ooh, my sequel.

This will long you and do my sequel as the route user.

You'll have all permissions, but we have a normal user account Now.

I've created a bob user account so we will log in that way.

My sequel, Space hyphen.

You user account name itches court Bob Space hyphen P.

And then it's going to ask for the password.

And 123456 And now we're officially along into my sequel from here.

We're going to do it, and I do control l Also, that's going to clear the screen.

And the first thing we're gonna do is show Dad, uh, basis.

So I want to see what databases we have it.

It's my sequel server s.

So we have the databases, we have the four normal default databases and then we have the class D B Class B B is the database that we're going to use.

Eso Then we use the use command through, then go into Class D B semi colon, of course.

And now we get some weird error deal with later, but mainly our database exchange.

That's the important thing from here.

We're going to do show tables to show us what tables are on this database semi colon.

And so it'll show us what tables are in class.

DBS way parts table and we have a vendor's table.

Let me do control well again.

And then from here, What we're going to do is we're going to describe the table, so d e s c on then we're going to describe the parts table, so this will show us basically what, the table with the schema of table looks like we're going to semi colon on.

Okay, so we got the field, the data type, whether it's a primary key, any extra information.

So we have a party, i d field that is an integer, and that is the primary key, and it gets auto increments.

So every time a new record is created, this part I d gets implemented by one.

Then we have a part name field that is text.

Then we have a part price field that is float so again when you're dealing with numbers and end is a whole number 10 11 550.

A float is a decimal point number 10 10.50 20.25 That's everything.

This is the part price.

We're gonna use a float, and then we have his vendor ideas.

This is the vendor.

I D.

Is what we're going to actually join joint with.

And so basically, the idea is that the this part is is offered or manufactured or whatever by this vendor, I d.

And that is going to be.

Then what we're going to do is we're gonna describe the vendor's table.

So the other tables the vendor's table, semi colon, we come in here is weak again.

We see field type extra.

So what a vendor I d.

So this is how we're going to be joining is based on with this vendor i d.

The vendor I d is an end and also in his auto increment it.

So every time you creating new vendor account auto increments by one, we have a vendor name that is text, and we have a vendor address that is text.

Now, obviously again, with these tables, these tables could be much, much, much, much, much larger.

I just made small tables here just just to make it easier to understand what's going on from that, we could do a select statement to see what's in these two dinner table.

So if we do select eso all So all the information from all the columns I slept off Rome and we're gonna do the parts table, so I call it.