Query98: "Query can have only one Outer Join" Error Message

MORE INFORMATION

WARNING: Your use or modification of the SQL statement provided in this article is at your own risk. Microsoft provides this SQL statement "as is" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. Microsoft does not support modifications of the SQL statement to suit specific customer requirements.

Creating the Data Source

1. Start Microsoft Query.
2. On the File menu, click New.
3. Select <New Data Source> and click OK.
4. Type two outer joins in the first text box.
5. In the Select A Driver list, click Microsoft 3.01 dBASE PPC.
6. Click Connect.
7. Locate and select the Microsoft Office 98:Sample Files:Sample Databases folder. Click "Select 'Sample Databases'".
8. Click OK in the Create New Data Source dialog box.

Creating the Query

1. Click to clear the "Use the Query Wizard to create/edit queries" check box.
2. In the Choose Data Source dialog box, click Two Outer Joins. Click OK.
3. Select Detail.dbf and click Open. Select Product.dbf and click Open.
4. Click Cancel.

Creating the Joins

1. On the Table menu, click Joins.
2. In the Left list, click PRODUCT.PRODUCT_ID. In the Right list, click DETAIL.PRODUCT_ID.
3. Select "ALL values from 'PRODUCT' and ONLY records from 'DETAIL' where PRODUCT.PRODUCT_ID = DETAIL.PRODUCT_ID".
4. Click Add. Click Close.
5. Double-click PRODUCT_ID and QUANTITY in the DETAIL table. Double click PRODUCT_ID and IN_STOCK in the PRODUCT table.
   
   This displays all the records in these four fields that match the query created in steps 2-4.
6. On the View menu, click SQL.
   
   The SQL statement displayed in the SQL dialog box should be:

   SSELECT DETAIL.PRODUCT_ID, DETAIL.QUANTITY, PRODUCT.PRODUCT_ID, PRODUCT.IN_STOCK FROM {oj 'hd:Microsoft Office 98:Sample Files: Sample Databases':'PRODUCT.DBF' PRODUCT LEFT OUTER JOIN 'hd: Microsoft Office 98:Sample Files:Sample Databases':'DETAIL.DBF' DETAIL ON PRODUCT.PRODUCT_ID = DETAIL.PRODUCT_ID}

7. Change the SQL statement to the following:

   SELECT DETAIL.PRODUCT_ID, DETAIL.QUANTITY, PRODUCT.PRODUCT_ID, PRODUCT.IN_STOCK FROM {oj 'hd:Microsoft Office 98:Sample Files: Sample Databases':'PRODUCT.DBF' PRODUCT LEFT OUTER JOIN 'hd: Microsoft Office 98:Sample Files:Sample Databases':'DETAIL.DBF' DETAIL ON (DETAIL.QUANTITY <= PRODUCT.IN_STOCK and PRODUCT.PRODUCT_ID = DETAIL.PRODUCT_ID)}

8. Click OK.
9. Click OK when you see the following message:
   SQL Query can't be represented graphically. Continue anyway?
   None of the records displayed have a value in the QUANTITY field that is greater than the corresponding value in the IN_STOCK field.

NOTE: The Table pane is no longer displayed because Microsoft Query cannot graphically represent the query with two outer joins that you created.