Self-Referential Join Example on Northwind Database

Self-Referential Join Example on Northwind Database

In the Microsoft Northwind database, there is an example of a self-referential table, Employee (see data model).

Let's look at some of the data with a simple query:

SELECT   EmployeeID,
         LastName,
         ReportsTo
FROM     Employees
ORDER BY EmployeeID

Which gives the data shown.

So you can see that EmployeeID=6 with the LastName=Suyama reports to EmployeeID=5 with the LastName=Buchanan.

So how do we get this all in one table?







The primary constraint here is that you cannot use the same table name twice in the same FROM statement. So, we need to use a table alias.

SELECT   Employees.EmployeeID,
         Employees.LastName,
         Employees.ReportsTo,
         Boss.EmployeeID,
         Boss.LastName
FROM     Employees
   JOIN  Employees Boss    ON Employees.reportsTo = Boss.EmployeeID

ORDER BY Employees.EmployeeID


Which gives this:

The FROM statement uses the Employees table twice, but one is aliased to be Boss. Based on that alias, the correct join is to use Employees.reportsTo as the foreign key, and Boss.EmployeeID as the primary key; because Employees report to Bosses.

Here's how that join looks in the Query Designer.

Regardless of how the database engine actually accomplishes the results, it's good to view this as two complete copies of the (same) table. They both have exactly the same fields and all the same records.