SQL Primer: Using Inner Join . . . On


This example shows how INNER JOIN is used to relate two tables (Order_Details2 and Products2) ON a common field (ProductID). The ProductName values are retrieved from the Products2 table.
OrderID ProductID ProductName Quantity UnitPrice
1 84 Sierras 5 $ 87
1 79 Polar Star 1 $ 310
1 78 North Face Sunspot 2 $ 390
2 84 Sierras 1 $ 87
2 82 Everglades 2 $ 98
2 79 Polar Star 1 $ 310
2 80 Big Sur 1 $ 200
3 82 Everglades 1 $ 98
3 84 Sierras 3 $ 87
4 79 Polar Star 2 $ 310
5 82 Everglades 1 $ 98
6 79 Polar Star 1 $ 310
7 82 Everglades 1 $ 98
7 80 Big Sur 2 $ 200
8 83 Rockies 1 $ 78
8 78 North Face Sunspot 2 $ 390
8 80 Big Sur 1 $ 200
9 82 Everglades 1 $ 98
10 79 Polar Star 1 $ 310
11 82 Everglades 1 $ 98
12 79 Polar Star 2 $ 310
13 84 Sierras 1 $ 87
14 82 Everglades 2 $ 98

The SQL Source of the Recordset is as follows:

SELECT Order_Details2.OrderID, Order_Details2.ProductID, Products2.ProductName, Order_Details2.Quantity, Order_Details2.UnitPrice FROM Order_Details2 INNER JOIN Products2 ON Order_Details2.ProductID = Products2.ProductID ORDER BY Order_Details2.OrderID

This Recordset includes fields from two tables. When selecting fields from multiple tables, the fields must be referenced as tableName.fieldName, especially if the same field appears in more than one table.

Example: Order_Details2.ProductID, Products2.ProductID

This is required to explicitly specify from which table the values of conflicting fields are retrieved, and returns an error if not defined.

Go Top   |   Return to DB101 ADO Samples