How to join
2 Select Statements In Sql
Querying Multiple Tables with SQL
Sharing buttons:
hi this is Larry what I want to talk to
you today is about how to create simple
queries for multiple tables you know I'm
just going to show you how we can
utilize the syntax you currently
understand and be able to do one of two
or more different tables in being able
to join them together with a common
simple syntax this is also known as the
implicit syntax for the inner join
because what we're doing here is doing a
simple inner join between tables meaning
that the tables themselves share a
common field and so therefore we're
going to be joining the table to be able
to collect the data based upon the
common fields that they have all right
so that's what we're gonna begin that's
what we're gonna cover today to start
out with one of the things you should be
understanding is the complexity of the
database itself for this example I'm
going to be utilizing the the database
for the product orders it's a sample one
that comes with a system and if you go
inside here you can see the different
tables right away here we have customers
items order details and orders the thing
to keep in mind is that even if you have
an order it's not one table for an order
because you'd have a lot of redundant
data you'd have a lot of duplication
like that redundancy
it also doesn't provide for file
independence alright or data
independence skills you have finally a
paid-up of data independence so what we
have is that you're gonna see is that
for every order you may have such things
as you have the order itself the
customers that we're ordering it the
details of the order meaning what items
were order I mean what specific details
there are and then there's the items
that it sells the descriptions and stuff
that are regarded the items or products
in some cases so you can see you may
have something for orders may have for
just an order you may have four or five
or six or seven eight of different
tables that you have to use that's why
what you're gonna happen up having to do
is do a lot of complex more than one
table queries alright so if we look at
this here if we look into customers we
can see what type of
columns are outside of here there's the
ID which is the P cave it's
automatically generated but then you
have the names address etc and then
inside the orders itself over here is
that you have order ID customer ID now
do you see any similarity between these
two tables at all well the common thing
is orders ID here right the customer ID
or screening the customer ID excuse me
are the same between both of these so
for the water table you have a customer
ID in there this should be declared as
the foreign key meaning that the foreign
key it represents the primary key in
another table this is where foreign keys
come very important but something that
you have to learn and understand that
1/4 of the full power of a foreign key
alright and so so when you look at this
you can see right away these two columns
these two tables have 8 shared comb
right so therefore we should be able to
write a select statement that is going
to be able to pull data from both of
those tables to create something we want
to do so to get started we'd write a new
query we just hit the new query button
okay and with this new query button we
want to select the database we want to
use to start in this case we're going to
use product orders all right and let's
just start a new query here so we want
to do is select for this query what we
want to do is select and find out for
the orders how many where the orders are
the order date and the zip code we want
to basically itemize this and order it
by zip code so we want to know how many
things were placed within as order
within a zip code and also some gives
some date reference so basically we want
to find the orders then replaced by zip
code all right and then the order date
with it so what we're gonna first to do
is we're gonna hit select what kind of
get over the screen select my Larry
select select and we want to do order ID
right and we're gonna do order day and
we're gonna do
we want to know the zip code so that's
getting called from the customer table
that's gonna be I think it's called
causes in okay and then we where do we
want to get this from well there are two
tables we get this from this is going to
be the orders table and the customers
table okay and you see what I had ways
since I put the two tables in now
instead of just having one table since
these were all from different tables
you're gonna see here I suppose I put
the tables in SQL Server the automated
checked out checking your code and it
says oh it works all right so we've just
said that and we've ordered from these
now what the big point here is that
unless you go in and declare what
columns you're going to put in as far as
you believe relighting to each other
you're gonna get a lot of bad data it's
just gonna come out and select all that
data from there but you want to do is
correlate things to make sure that
you're getting the correct numbers from
that from that from for the columns okay
so we're going to say what you need in
here every time you do any time you do
an inner join basically what you're
gonna be doing is looking for the common
columns between there and align them up
in a where statement in this case and
where's David you can use a joint
statement also and joint overall in the
long term are good to know and
understand and practice because you may
have more than an inner joint but inner
joints are pretty are pretty much
standard and what you're gonna be doing
closely and it's good to see it in this
manner because most of you probably want
to select statements have a long table
ready so in this case here we want to
join the two tables together so we're
going to know we need to do orders where
orders dot cust ID right or its orders
not or orders dot cust ID right and then
we're going to go equals we're gonna go
equals to customers God cust ID and so
what we've done here is very simply just
say select these columns from the orders
and customers tables but we're going to
match up with a customer ID it was a
customer ID in both of them
if the warranty has been set up
correctly you're going to find that
you're going to get the data accurately
between these two tables because of that
relationship now what I want to also do
is just an order by in this case we're
going to go order by customer zip and
we're also going to day or day just to
show you we can do this so we can do it
from both tables here in the corner so
we have a nice little query here and
let's run this right away and see
execute this and see what comes up and
as you can see here we already have the
order of the information coming up
pretty quickly and let's go through it
there's a lot of orders in air that meet
this criteria and if you notice for
every customer then like in this
customer and the zip code for the zip
code excuse me you notice here there's
two different orders that were placed
okay within the zip code okay and
different days so you can pull patterns
and data right from this this bunch of
data you have you could probably gather
some information but if you can see some
like this of code is more active than
the other one so anyways data provides
you to be able to help some information
what I want to show you is it can go in
there and simply do this with two tables
or more using it for dinner joining this
is probably the type you're gonna be
utilizing more more and more
okay more often than that so let's just
go in here there and let's take a look
at and do it another query with more
than two tables okay and let me see I
got it saved over here yeah pop it in
here so in this case here let's just
read through this I'm selecting the
order date item the item ID quantity
customer first name customer last name
so basically I'm looking for the first
and last name of people and I wanted to
find out what item they order by either
an ID without the description and then
the quantity of the order so I can get
some kind of data on the customer but
I'm gonna need to take this from three
different tables because you look here
we're going to take from the orders
table we're going to take from the order
details table and we're going
from the customers table because the
customers table is what has customers
first name and last name the orders
table is what the guiding point is we
want to know where the order date is and
then order details such as item ID and
the quantity come from the order details
so we need to have all three of those
people put together and we're going to
inter join them so we're going to
mention the three and the front tables
in there but key here is this for every
relationship you need to identify your
going to need identify the different
types of where the columns are supposed
to be connected in this case we know we
have to go to order and get the order
ID'd equal order ID details in order
details we also know we have to go in
orders and then detail the customer ID
to you with a customers table all right
so understand every time every time you
want to mo to multiple tables okay
you're gonna have to do or where
statements that match up the different
foreign keys or the tables that are
common each other to make this work in
this case this last one I'm ordering my
customer last thing so let's just say
execute this and it comes out on the
bottom here and you can see we get this
data right here so when you see the
customers is Bailey there's Blancas got
a lot of orders chaddock's one in
Gunther Jacob has a lot to order all
right and we can go through here to find
this information okay so that's just
gives you an example of how to do this
for multiple tables with an inner join
which is kind of the most common way
you're going to be doing things
technically if you're not do an inner
join you're gonna be looking for other
types of joins and relationships aisle
they give much more complex and I wanted
to show you here how to do with the
simple select statement that you've been
used to doing
