query

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