Placeholder Image

字幕表 動画を再生する

  • creating these classes requires equipment and service.

  • 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.

  • Okay, so we have a part I d So we have a caw go with a part idea of one with a park price of 11.5 with a vendor i d of one.

  • We have a rod part Idea of two part price of $2.50.

  • Vendor I D of two.

  • We have a sprocket $5 vendor idea, three nut dollars and 50 cents vendor idea of one so on and so forth.

  • And then we could do a select all from vendors semi colon.

  • And then here we can see is we have vendor.

  • I'd even variety of 12 or three.

  • So getting this corresponds to this, right?

  • This corresponds to this.

  • And this corresponds to this.

  • Then for those vendors, we have Su's sprockets out of California.

  • We have the mega Cog Corp out of Texas and we have wickets are us and they are out of Oregon.

  • Right?

  • So this is how you can have two separate table.

  • So you have one table and all this one table does is it focuses on the parts.

  • So you only put the information about the parts in there and the defenders.

  • This is one table only for the vendors.

  • And so basically, how you're able to connect the two tables is if you want to say okay, the Kaw ge is being sold to us by, you know, Suz Sprockets.

  • Instead of having to put all of the information for Sue Sprockets into this part's table, you can simply reference it with the vendor I d.

  • And then you'll be able to connect using this thing called the inner Joy.

  • So from here, or ideal, they already control l declare the screen and now we're going to do the under joint.

  • So we're going Thio select.

  • Let's just select us.

  • We're gonna select all columns, right?

  • And then we're gonna say from hearts, um and they were gonna say inner joy.

  • So we're using inner join here, So we're gonna select all from the parts.

  • So the first table that we're going to be dealing with and we're gonna say inner join.

  • So joining this with another table s, we're gonna say inter, join on inner join vendor CE, so we're joining parts with vendors and then we're going to say on and then Here's where I get to the naming conventions.

  • We do parts dot then nor underscore i d So the parts table the vendor i d field.

  • So we're joining that when I say that equals the vendor's table dot vendor underscore idee field.

  • And then we're going hiss semi colon.

  • And hopefully if I didn't that finger this this will work.

  • Okay, so now we're actually getting both tables are actually.

  • Now, join together.

  • Let me adjust the font size here, so we gotta go here.

  • Oh, our shrink this down 15 to make this a little easier to see and see now.

  • So basically, see, before we before we were, we had two tables and they're two entirely different tables.

  • Now, by using that inner join, we're actually able to connect those two tables together and were able to interact with those two tables as if they're one single table.

  • So, you know, caw ge part price of $11.50 again, we got this vendor I d.

  • So this is what joins the two together.

  • So we know the cause.

  • Eggs vendor's name is Sue Sprockets and vendors addresses California the sprocket for, you know, $5 is joined based off of that vendor I d.

  • That's provided by wickets are us out of Oregon, you know, again, the washer is $6.25 joined Joined off the vendor I D that is being provided by the mega cog out of Texas.

  • Right?

  • And so that's how you're able to combine those two together.

  • And so one of the things that you can do then is you do like different statements.

  • So instead of doing a select all possibly now we could do, let's say select, let's say, um uh, part, Let's say, oh, you're gonna say select a part underscore name um, from parts dinner join, uh, vendors on, uh, parts got vendor underscore I d equals vendors.

  • Stott vendor underscore i d.

  • There were news use where?

  • Where?

  • Um been door underscore Name equals, let's say mega cog.