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

- Professional Edition of Microsoft Visual Basic for Windows,

  version 3.0

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.

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

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

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

REFERENCES

This reference is from the BIBLIO.MDB database: "Using SQL," 1990 Groff, James R., Osborne McGraw-Hill, Berkeley, CA