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

By: Dallas Snider   |   Updated: 2015-02-24   |   Comments (2)   |   Related: > JOIN Tables

Problem

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?

Solution

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
go

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.

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.

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.

• SQL Server Join Example
• Joining data using UNION and UNION ALL in SQL Server
• SQL Server SELECT Tutorial
• Our complete tutorial list

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips