How to join
5 Tables In Mysql
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
