Microsoft KB Archive/114592

= How to Construct a Self-Join Query in Visual Basic 3.0 =

Article ID: 114592

Article Last Modified on 1/8/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q114592



SUMMARY
A self-join is a special form of query involving a relationship that a table has with itself. Because the field names and table name in a self- join are identical, you must specify them in an unambiguous way in the body of the query. In order to construct the query, the table name must be aliased, and the fields or columns may be aliased as well.



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 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

