Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


How to Join to the Same Table Multiple Times for a SQL Server query

By:   |   Read Comments (2)   |   Related Tips: More > JOIN Tables

Free Webcast - Simplify SQL Server Administration


I need to retrieve information for multiple employees from a single employee table in one T-SQL query, is there a simple way to do this in SQL Server?


In this tip, we will write a query to join a table containing multiple employee IDs within a single sales table to an employee table. Using T-SQL in this manner is often found in data warehouses where we need to join a fact table with multiple foreign keys to the same dimension table. Also, it is very important to accurately utilize aliases whenever you join to the same table more than once within the same query.

Let's begin by creating our tables using the T-SQL create table statements below. We will create an Employee table and a Sales table. The Sales table has three columns with foreign key references to the Employee table. These columns are the employee IDs for the sales person, the department manager and the store manager.

use MSSQLTips

create table dbo.Employee
  pkEmployeeID int identity(1,1) primary key,
  EmployeeNumber integer not null,
  FirstName varchar(20) not null,
  LastName varchar(20) not null

create table dbo.Sales
  pkSalesID int identity(1,1) primary key,
  SalesDateTime datetime not null,
  SalespersonID int not null foreign key references Employee(pkEmployeeID),
  DepartmentManagerID int not null foreign key references Employee(pkEmployeeID),
  StoreManagerID int  not null foreign key references Employee(pkEmployeeID),
  Amount money not null

Next, we will populate our tables with the T-SQL below.

insert into dbo.Employee values (100, 'Mary', 'Salesperson')
insert into dbo.Employee values (200, 'John', 'DepartmentManager')
insert into dbo.Employee values (300, 'Jane', 'StoreManager')
insert into dbo.Employee values (400, 'Jim', 'Salesperson')

insert into dbo.Sales values (getdate(), 1, 2, 3, 4.56)
insert into dbo.Sales values (getdate(), 4, 2, 3, 1.23)

After inserting the records, let's review the data to make sure it is correct.

Review the data

The T-SQL query to accomplish our task appears in the box below.

  • In the single query listed below we will join the Sales table three times to the Employee table as shown in lines 12-14.
  • In line 12 we create an alias for the Employee table named sp that represents the salesperson's data which we show in line 6.
  • In line 13 we create an alias for the Employee table named dm that represents the department manager's data which is displayed in line 8.
  • In line 14 we create an alias for the Employee table named sm that represents the store manager's data which is selected in line 10.
  • In lines 6, 8 and 10 we create aliases for the columns so we can differentiate among the salesperson, department manager, and store manager.

select                                                                      --01
s.pkSalesID as SaleID,                                                      --02
convert(nvarchar(20),s.SalesDateTime,120) as SalesDateTime,                 --03
s.Amount,                                                                   --04
s.SalespersonID,                                                            --05
sp.FirstName + ' ' + sp.LastName as SalesPerson,                            --06
s.DepartmentManagerID as DeptMgrID,                                         --07
dm.FirstName  + ' ' + dm.LastName as DepartmentManager,                     --08
s.StoreManagerID as StoreMgrID,                                             --09
sm.FirstName + ' ' + sm.LastName as StoreManager                            --10
from dbo.Sales as s                                                         --11
left outer join dbo.Employee as sp on s.SalespersonID=sp.pkEmployeeID       --12
left outer join dbo.Employee as dm on s.DepartmentManagerID=dm.pkEmployeeID --13
left outer join dbo.Employee as sm on s.StoreManagerID=sm.pkEmployeeID      --14

The results of the query are shown below.

Query results
Next Steps

It is imperative that queries like the one above are thoroughly tested to ensure correct usage of the aliases. It would be easy to mix up the names with the wrong employee ID. If the syntax of the T-SQL statement is correct, SQL Server will not give you an error stating that you have misused your aliases. Furthermore, if the tables you are joining have good referential integrity you can consider changing the left outer joins to inner joins. One final thought, you can make your table aliases more descriptive to help with code readability. We could have used 'salesPerson' for the alias instead of 'sp' in the above query.

Check out these other tips regarding joins in SQL Server on MSSQLTips.com.

Last Update:

next webcast button

next tip button

About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Send me SQL tips:


Saturday, December 12, 2015 - 7:58:00 AM - Altab Hossen Back To Top

Thank You Boss


Friday, August 21, 2015 - 3:58:50 AM - Nikhil Back To Top

Super like. This was EXACTLY WHAT I NEEDED. THanks

Learn more about SQL Server tools