Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

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.

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:






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





More SQL Server Solutions











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 


Get free SQL tips:

*Enter Code refresh code     



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