Join Database Tables On Multiple Fields

If you’ve spent much time on back-end development you probably have at least a basic understanding of database joins - what they are and how they work. At a basic level, joins are a mechanism to utilize primary and foreign keys to fetch related information from multiple tables. In most cases we use a single field from each table to make the connection, but what about a situation where a table may have multiple references to another table? Let’s look at a scenario where that may be required and how we can work with it.

The example here is an employee evaluation application. We have a table that contains our company (Pied Piper) employees, and another table that contains the results of our staff evaluations. The evaluation results needs to contain that staff member who is being evaluated as well as the staff member (supervisor) who performed the evaluation. When querying the evalaution table we want to include the name of both the staff member and supervisor, requiring us to join the ‘Evaluations’ table to the ‘Employees’ table on 2 different fields.

Here is a simple ‘Employees’ table in a database that contains information on Pied Piper employees.

employeeID firstName lastName
1 Erlich Bachman
2 Richard Hendricks
3 Dinesh Chugtai
4 Bertram Gilfoyle
5 Nelson Bighetti

Now let’s look at the ‘Evaluations’ table. The ‘staffID’ field will reference that ‘Employees’ table as the person who is being evaluated. The ‘supervisorID’ field will also reference the ‘Employees’ table as the person performing the evaluation.

evaluationID staffID supervisorID evalDate score
1 4 2 2018-12-01 Excellent
2 3 2 2018-12-01 Excellent
3 3 4 2018-12-15 Poor

From looking at this information we can tell the first evaluation was for Bertram Gilfoyle and was performed by Richard Hendrix. How can we write a query that will include the evaluation results along with the names of both the staff member being evaluated and the supervisor?

The trick here is to use aliases. An alias is a way to refer to a database object (field, table, etc) using a different name. In our example we’ll be using an alias on the firstName and lastName fields so we can indicate which name is the staff member and which name is the supervisor. We’ll also need to join the ‘Employees’ table to the ‘Evaluations’ table twice, each time using a different alias.

  
    select
      evalDate,
      score,
      staff.firstName as staffFName,
      staff.lastName as staffLName,
      supervisor.firstName as supervisorFName,
      supervisor.lastName as supervisorLName
    from Evaluations
      inner join Employees as staff on
        staffID = staff.employeeID
      inner join Employees as supervisor on
        supervisorID = supervisor.employeeID
    where evaluationID = 1
  

The results for this query will look like the following:

evalDate score staffFName staffLName supervisorFName supervisorLName
2018-12-01 Excellent Bertram Gilfoyle Richard Hendrix