Quick References
      ADO
      ASP
      CSS2
      HTML
      JavaScript
      Jet SQL
      VBScript
      WML
      WMLScript
      WSH
      XHTML
      XML DOM
      XSLT

Features
      Knowledge Base
      Tutorials

Partners
     ZVON.ORG
     XML
     Planet Source Code
     VisualBuilder
     Web Design
     Your HTML Source
     XML/XSLT Forums
     ASPAlliance
     Scripts
     
     Programmers Heaven
     Tek-Tips Forums
     Developer Fusion
     Code Project










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-2005 by Infinite Software Solutions, Inc. All rights reserved.
Trademark Information