MORE INFORMATION
A self-join is a query that requires two copies of a single table for its
result. The NWIND.MDB sample database supplied with Microsoft Access
version 2.0 is used as an example.
The task of the example is to find the manager's name for all employees who
have a manager. You need to use a self-join on the Employees table because
even managers are employees and are listed in the same table (Employees).
Here's the logic of the self-join query:
Examine all possible pairs of rows in Employees -- one from the first
copy and one from the second copy. Then retrieve the names of both
the employee and his or her manager if and only if the value in the
Reports To field in one copy matches that of the Employee ID field in
the second copy.
To implement this logic, you need to reference two rows from the Employees
table at the same time. To distinguish between the two references, you need
to introduce arbitrary range variables, such as Employees_1 and
Employees_2, over the Employees table. At any particular point, Employees_1
represents some row in the first copy of Employees, and Employees_2
represents some row from the second copy.
The query, in the Microsoft Access dialect of SQL is:
SELECT DISTINCTROW Employees_1.[Employee ID],
Employees_1.[First Name], Employees_1.[Last Name],
Employees_2.[First Name] AS [Manager FirstName],
Employees_2.[Last Name] AS [Manager LastName]
FROM Employees AS Employees_1, Employees AS Employees_2,
Employees_1 INNER JOIN Employees_2 ON
Employees_1.[Reports To] = Employees_2.[Employee ID];
Or you could have simply aliased only the second or duplicate copy of the
Employees table:
SELECT DISTINCTROW Employees.[Employee ID],
Employees.[First Name], Employees.[Last Name],
Employees_Dup.[First Name] AS [Manager FirstName],
Employees_Dup.[Last Name] AS [Manager LastName]
FROM Employees, Employees AS Employees_Dup,
Employees INNER JOIN Employees_Dup ON
Employees.[Reports To] = Employees_Dup.[Employee ID];
Also, you don't really need to alias the columns or fields returned from
the second copy of the table to disambiguate those fields from those in the
first copy, because the use of the As <aliasname> on the table name does
that for you. However, to make the results more meaningful, it is helpful
to alias the field or column names as well.
In other words, the following will also work:
SELECT DISTINCTROW Employees.[Employee ID],
Employees.[First Name], Employees.[Last Name],
Employees_Dup.[First Name],
Employees_Dup.[Last Name]
FROM Employees, Employees AS Employees_Dup,
Employees INNER JOIN Employees_Dup ON
Employees.[Reports To] = Employees_Dup.[Employee ID];
Visual Basic Code Example
The following Visual Basic code demonstrates this:
Sub Command1_Click ()
Dim db As database
Dim ds As dynaset
Dim sql As String
sql = sql & "SELECT DISTINCTROW Employees_1.[Employee ID],"
sql = sql & "Employees_1.[First Name], Employees_1.[Last Name],"
sql = sql & "Employees_2.[First Name] AS [Manager FirstName],"
sql = sql & "Employees_2.[Last Name] AS [Manager LastName]"
sql = sql & "FROM Employees AS Employees_1,Employees AS Employees_2,"
sql = sql & "Employees_1 INNER JOIN Employees_2 ON "
sql = sql & "Employees_1.[Reports To] = Employees_2.[Employee ID]"
Set db = OpenDatabase("c:\access\nwind.mdb")
Set ds = db.CreateDynaset(sql)
Do Until ds.EOF
' Enter the following three lines of code as one, single line:
Print "Employee "; ds![Employee ID], ds![First Name],
ds![Last Name], "Managed by "; ds![Manager FirstName],
ds![Manager LastName]
ds.MoveNext
Loop
ds.Close
db.Close
End Sub