OPERATION:  INNER JOIN
SELECT * | list FROM table1
INNER JOIN table2 ON table1.field1 compoperator table2.field2
 
The INNER JOIN operation can be used in any FROM clause to combine records from two tables. It is, in fact, the most common type of join. There must be a matching value in a field common to both tables.
 
An INNER JOIN cannot be nested inside a LEFT JOIN or RIGHT JOIN.
 
The following example returns a list of all employees who live in Boston and who are working on the Hardwork project:
 
SELECT Employee.Username
FROM Employee INNER JOIN Project
ON Employee.EmployeeID = Project.EmployeeID
WHERE Employee.City = 'Boston'
AND Project.ProjectName = 'Hardwork';
 
Note that you can join any two numeric fields as long as they are of like type (such as AutoNumber and Long). However, with non-numeric data, the fields must be of the same type and contain the same kind of data, though they can have different names.
 
With the INNER JOIN operation any relational comparison operator can be used in the ON clause: =, <, >, <=, >=, or <>. The following example returns all cases where the value in the EmployeeID field of Employee matches that in the EmployeeID field of Project (i.e. it returns the names of those employees working on each of the projects).
 
SELECT Employee.username, Project.ProjectName
FROM Employee INNER JOIN Project
ON Employee.EmployeeID = Project.EmployeeID;
 
...whereas this example returns all employees not working on each project:
 
SELECT Employee.Username, Project.ProjectName
FROM Employee INNER JOIN Project
ON Employee.EmployeeID <> Project.EmployeeID;
 
You can also link several clauses in a INNER JOIN statement: the following example returns all employees working on each project who live in the same city as where the project is taking place:
 
SELECT Employee.Username, Project.ProjectName, Project.Location
FROM Employee INNER JOIN Project
ON (Employee.EmployeeID = Project.EmployeeID)
AND (Employee.City = Project.Location);
 
And you can also nest statements as in the following example which returns all tunes recorded by musicians who are members of duos:
 
SELECT Tunes.Name, Musicians.Name, Duos.Name
FROM Tunes INNER JOIN (Musicians INNER JOIN Duos
ON (Musicians.Name = Duos.Member1)
OR (Musicians.Name = Duos.Member2))
ON Tunes.Musician = Musicians.Name;
 
An inner join can also be achieved by using the WHERE clause. The following query returns the same set of records as the previous example:
 
SELECT Tunes.Name, Musicians.Name, Duos.Name
FROM Tunes, Musicians, Duos
WHERE ((Musicians.Name = Duos.Member1)
OR (Musicians.Name = Duos.Member2))
AND (Tunes.Musician = Musicians.Name);
 
Microsoft warns, "If you try to join fields containing Memo or OLE Object data, an error will occur."

Copyright 1999-2001 by Infinite Software Solutions, Inc. All rights reserved.
Trademark Information