join

How to Join 3 tables in 1 SQL query



Sharing buttons:

a relational database has many related

tables inside of it these two tables

relate on a location ID field this gives

us the ability to take these two tables

and put them in the same query using a

join we also have the ability to join up

to 256 tables in one query we're going

to start off by doing a recap of a two

table join and then expand it to a three

table join we can see that Alex Adams

works at location 1 which is Seattle

Washington Barry Brown also works in

location 1 which is Seattle Washington

Leo Sacco works in location 2 which is

on 2nd Avenue in Boston this second

table is known as a lookup table and it

contains more detailed information about

that location as referenced by the

location ID in the employee table let's

again repeat the process of exploring

related tables and then joining those

two tables and let's use the example

with the grant table go select all

fields from grant and run it we can see

that we have a grant ID a grant name and

this third field is the employee ID who

procured that grant who is employee 7

well we can query the employee table and

we notice the employee table has an

employee ID and number 7 is david

lauding whose employee - that's Barry

Brown now let's put both of these tables

in the same from clause like so put the

word in or join between the table names

let me a leus these tables grant will be

aliased as gr employee will be aliased

as e/m in the on clause let's specify

that both these tables have the M PI D

field in common

GRE M PI D equals a mm PI D and run it

now we see the listing of the grants as

well as the names

the employees who procured grant let's

narrow our field selection list down so

we just see the grant name the amount

the first name of the employee who found

the grant and the last name of the

employee who found the grant and what we

really have here is something that looks

like a single table could we join this

result set on another table well what

will we join it on David Lonnie I think

he's from Seattle what other employees

are from Seattle how many of these

grants were found from Seattle how many

were found from Boston and how many were

found from Chicago or Spokane well the

location ID field is in the employee

table but that's just a number what if

we want the city name to appear in our

report well what we're going to need to

do is take the employee table and join

it to the location table the location

table is not a part of this query yet

but what we can do is make another inner

join to the location table and join it

on the employees location ID being

associated with the locations location

ID and when I run it it says I have an

ambiguity oh that's because location ID

needs to come from a specific table here

is the new result set showing the

location ID field we also want to

include from the location table the city

field now we can see exactly which

grants were found in which cities if we

just wanted to see a city report we

could actually get rid of these three

fields

and just have a listing of the grants

and what cities they're from lab 2.2

skill check one show all the city names

and rates of pay for each employee in

those cities you will need to join the

location employee and pay rates table

show the city field from the location

table include the first name and last

name from the employee table and show

all fields from the pay rates table when

you're done your screen should resemble

the figure you see here