SQL: Join Multiple Tables

If you know how to join 2 tables, I have good news for you. Joining more than 2 is a piece of cake.

Let’s take the example below. We have three tables. JobTitle holds a list of Job Titles. Table JobType holds a list of Job Titles (Supervisor, Technical, Hourly, etc). Finally table Employee holds employee names, addresses and emails.

These tables are related through primary keys(PK) and foreign keys(FK). It is through these PK and FK that we will connect these tables.

3join.jpg

If we want a list of Employees like this : Name, Address, Email, JobTitle and JobType, we would use the syntax below:

select Name, Address, Email, JobTitle, JobType
from Employee as e join JotTitle as j 
          on e.jobid = j.jobid join JobType as jt
          on j.jobTypeID = jt.jobTypeID

**remember MS SQL Server is not case sensitive by default

So as you can see, literally all we had to do is repeat the steps we did to join our first two tables.

If you would like to try an example in the AdventureWorks2012 database, try this:

 select loginid, name, StartDate
 from HumanResources.Employee as e 
 join HumanResources.EmployeeDepartmentHistory as h
 on e.BusinessEntityID = h.BusinessEntityID join HumanResources.Department as d
 on d.DepartmentID = h.DepartmentID

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s