MySQL Database Tutorial - 22 - How to Join Tables

Sharing buttons:

alright guys welcome to your 22nd MySQL

tutorial and in this lesson I'm going to

be talking to you guys about one of the

coolest most important and also powerful

features of MySQL and that is how to

join together tables so let's go ahead

and take a look at our tables we have so

far we have a customers table which

pretty much all the users of the website

this is their personal information and

also an item's table because remember

our website was kind of like eBay where

the user can list an item and other

people can bid on it so check it out

what if our boss comes in and he's like

Bucky these items you're doing a pretty

good job on these you have the name of

the item the cost of the item the number

of bids that's a pretty good information

but we also need the sellers information

whenever you list the item in the table

you need the sellers name and address in

zip code and state therefore whenever

anyone is bidding on your item you know

then they know if they win it where to

send the money to or whenever they win

your item they can expect they know

where the item is coming from to

calculate shipping cost so we say ok

that's going to be a problem so you're

telling me that I have to redesign my

entire items table to include the

sellers name address zip code on each

row well check this out first of all

whenever you have a user that is selling

like 20 items that means that we would

have 20 different rows that say the same

thing Bucky Roberts Bucky Roberts Bucky

Roberts Bucky Roberts new york new york

new york new york new york ok so that

can get kind of repetitive and we know

that any time we see repetitive

information in mysql that means we

probably did something wrong aside from

that say that i'm a user on this website

and i have like i said 20 or 30 items

listed now I'm looking at my address and

I accidentally typed North Carolina

instead of New York is my state so does

that mean that I'm going to have to go

through every item and change my address

from New York to North Carolina or vice

versa well that can be a pain in the

butt too

so what can we do to solve this problem

because we can't get away from the fact

we need the seller's information on each

row well thankfully for you in MySQL you

can do something called join tables

together and whenever you do this you

create a temporary table that pretty

much grabs information from any table

that you want it to it makes a temporary

table with all the information you want

so let me go ahead and show you guys how

to do this real quick say our boss says

ok I won't make you get all this

information but you at least need the

customers and guys the customer is

pretty much like the user you need the

users ID in the user's name and you also

need the items name that they're listing

and the cost of the item so for each

item you also need the users ID and the

user's name and we say ok the only

problem is some of the information is in

our customers table and some of the

information is in our items table so how

the heck do we want to do that

well check this out whenever I made this

table I arranged it in very specific way

and I did it for a very specific purpose

in our items table we not only have the

name of the item and the price of that

item but we also have the ID number of

who listed that item so for example the

seller ID of this used diaper for my

sister is 1 so therefore we know that in

our customers table whoever has the ID

of 1 that's who's selling the used

diaper who happens to be me who lives at

Hungerford Ave Adams in New York 1 3 605

so that way instead of having to type

all the other information we can just

reference their ID number and then you

can hop over to the customers table and

say hey that ID number is this person or

the person who was selling that car is

Cynthia because her seller ID was 6 but

how do we take this information and

combine it into a new table well in

order to do that I'll show you go ahead

and use your select statement just like

before and what you need to do is you

need to type the name of the columns

that you want to use from each table but

check this out in customers we have a

column named ID ok that's nice why are

you telling me this well in items we

also have a cow

name ID okay so that can get kind of

confusing whenever you're typing in -

crow and you say select ID my SQL is

going to look at you in BEC hey what ID

do you want me to select the customers

ID or the items ID in order to do this

we need to use fully qualified names so

remember customers da ID means okay

select the ID column from the customers

table we also want to select the

customers name from the or excuse me the

name from the customers table because

remember name exists both in items and

customers so now what we want to do is

we want to select two more columns our

boss said items name and items cost so

now we selected four columns that we

want to make up our brand new table that

our boss required so now we add a from

and we say okay these are the columns

now what tables is this information

coming from the customers tables and the

items tables now whenever you are making

a join just go ahead and hit all or

excuse me list all of the tables that

you want to join and separate them with

a comma but make sure not to put a comma

after the last one now after this you

need to add a where clause and this

where clause is basically saying okay

how are these tables related well

they're related because the customers ID

is equal to the seller ID now by default

MySQL doesn't know that the seller ID in

items is actually the ID number of the

customers so that's pretty much the

heart of this query that's where we need

to tell it how and where the tables are

related they're related right here so

now I'm just going to add a simple order

clause order by customers ID now let me

just go ahead and copy this in case I

have NER run this baby and check it out

we now have a new custom table with all

of the information that our boss

required without having to edit our

customers table or item

staple for each of the sellers we listed

their ID number my ID number was number

one the name of the sellers and these

two bits of information are from the

customers table now these last two

columns of information are from the

items table for each of the sellers we

listed the items they're selling and

also the cost of the item so now check

this out instead of going into our items

table and having to write Bucky Roberts

Bucky Roberts for all my items and then

in case we need to edit my name we don't

need to edit that ten or twenty or fifty

times for each item the only thing we

need to do is hop over to this customers

table and say okay I'm just going to

edit this once and there you go so it's

a whole lot easier in like I said any

time you are working with joining tables

in MySQL what you do is you go ahead and

you select all of the columns that you

want to use in your new table and make

sure to use the fully qualified name so

MySQL doesn't get confused and then you

write from and select what are the

original tables that you're selecting

those columns from next you write your

where clause in your where Clause is

basically how your tables are related

what do they have in common and also if

you want to order by them you know just

organize them in any fashion you can

sort them with the order by clause I

decided to order mine by customers ID so

therefore go I can't even talk now I

talked a lot during this tutorial I

might be running out of time but there

you go there is your simple tutorial on

how to join tables and in the next

lesson I'm going to be showing you guys

an alternative syntax that may be a

little easier but anyways thank you guys

for watching don't forget subscribe and

I'll see you guys in the next video