Self-Joins

It’s possible to create a join between a table and a second instance of the same table. A common example is an Employees table in which the ReportsTo field contains EmployeeID values found in the Employees table itself.

The following example joins the Employees table to itself. The ReportsTo field in the first instance of the Employees table is linked to the EmployeeID field in the second instance. The second instance of the Employees table is given the alias Supervisors:

SELECT 
	Employees.EmployeeID, 
	Employees.LastName, 
	Supervisors.FirstName & ' ' & Supervisors.LastName AS SupervisorName
FROM Employees 
INNER JOIN Employees AS Supervisors 
ON Employees.ReportsTo = Supervisors.EmployeeID;

In this example, a calculated field called SupervisorName is created. Note the use of the & concatenation operator, which is used to combine the output of the FirstName and LastName fields in the Supervisors table.

Because an inner join was specified, if the value in the ReportsTo field of any given record isn’t also found in the Employees table, the record isn’t selected. If you want to select the record even when the self-join doesn’t find a match, you can specify a left join, as shown in the earlier examples in this chapter.

The following revised query specifies a left join. The calculated SupervisorName field has been revised to anticipate that certain records may have a Null value in the ReportsTo field (perhaps indicating that the employee is a supervisor, and doesn’t report to another employee in the Employees table). The calculated field returns the specified value “No Supervisor” for those records in which the ReportsTo field is Null:

SELECT 
	Employees.EmployeeID, 
	Employees.LastName, 
	IIf(IsNull(Employees.ReportsTo),
		'No Supervisor',
		Supervisors.FirstName & ' ' & Supervisors.LastName) 
		AS SupervisorName
FROM Employees 
LEFT JOIN Employees AS Supervisors 
ON Employees.ReportsTo = Supervisors.EmployeeID;

Note This query uses the IIf (Immediate If) function in the calculated expression, which creates the field SupervisorName. This is one of many built-in functions understood by Microsoft Jet. Other functions you can use include string-manipulation functions and date functions. While these functions can be extremely useful, they should be used with care. Because they aren’t available on all back-end database systems, they must be evaluated locally by the Jet database engine running on the user’s computer. If calculated fields using these functions are part of the selection criteria, a table scan (a serial read) of all records in the result set must be performed so that the condition can be evaluated locally. Rather than adding a calculated field directly to the query itself, consider creating calculated fields in the form or report to display the query results.