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

ID: Q114592

The information in this article applies to:

Microsoft Visual Basic Professional Edition 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.

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

REFERENCES

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

Additional query words: 3.00

Keywords :
Version : WINDOWS:3.0
Platform : WINDOWS
Issue type :