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

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