SELECT – Join

This combines two tables using a join field common to both tables. ADOCE uses a subset of the fields in the combined database to build a recordset.

Syntax

SELECT [tablename.]fieldname [,[tablename.]fieldname ...] FROM tablename INNER JOIN tablename2 ON tablename.fieldname = tablename2.fieldname2

Parameters

fieldname

Specifies the name of a field in the table to include in the recordset.

tablename

Specifies the name of the table from which to retrieve data.

Remarks

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 must specify <tablename>.ProductID as the fieldname, where <tablename> is the name of a table that contains a field named ProductID.

You can use an INNER JOIN operation in any FROM clause. This is the only type of join supported in ADOCE. INNER JOIN combines 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.

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.

A recordset returned by a JOIN statement with one or more columns from a single table can be updated. If a returned column has data from more than one table, it is read-only and cannot be updated.

INNER JOIN clauses can be nested to create complex queries. Brackets must be used to enclose table names that contain spaces.

Examples

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.

The following SQL statement 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