This statement enables two tables to be combined using a join field common to both tables. ADOCE uses a subset of the fields in the combined database to build the recordset.
SELECT [tablename.]fieldname [,[tablename.]fieldname ...] FROM tablename INNER JOIN tablename2 ON tablename.fieldname = tablename2.fieldname2
Because the tables being combined may have fields with the same name, it may be necessary to specify the table name with the fieldname. For example, if more than one table has the field ProductID, the table name is not optional and you will need to specify Table1.ProductID as the fieldname.
You can use an INNER JOIN operation in any FROM clause. This is the only type of join supported in ADOCE. Inner joins combine records from two tables whenever there are matching values in a field common to both tables.
If you try to join fields containing Memo or OLE Object data, an error occurs.
The following SQL statement shows how you could join the Categories and Products tables on the CategoryID field:
SELECT CategoryName, ProductName
FROM Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID;
In the preceding example, CategoryID is the joined field, but it is not included in the query output because it is not included in the SELECT statement. To include the joined field, include the field name in the SELECT statement. In this case, the field name is Categories.CategoryID. You must specify the table name when a field name is used in more than one table.
You can join a maximum of four tables, and only inner joins on equality are supported. Joins are possible only with the above explicit INNER JOIN syntax. Implied joins using the WHERE clause are not supported.
At least one of the tables in each join clause must be indexed on the joined field.
Joins that return columns from only a single table, such as the project list following the SELECT clause, have results that can be updated. All other joins produce read-only result sets. INNER JOIN clauses can be nested to create complex queries. Brackets must be used to enclose table names that contain spaces. The following example shows a complex query using INNER JOIN.
SELECT Orders.ShippedDate, Shippers.CompanyName, Orders.ShipName,
Products.ProductName, [Order Details].UnitPrice,
[Order Details].Quantity, [Order Details].Discount
FROM Shippers INNER JOIN
(Products INNER JOIN
(Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON
Products.ProductID = [Order Details].ProductID) ON
Shippers.ShipperID = Orders.ShipVia
WHERE (((Orders.ShippedDate) >= #5/1/96# And
(Orders.ShippedDate) <= #5/31/96#))
ORDER BY Orders.ShippedDate