MSQuery Err Msg: "Query Can Have Only One Outer Join"

The information in this article applies to:

• Microsoft Query for Windows, version 1.0
• Microsoft Excel for Windows, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Office 97 for Windows

SYMPTOMS

In Microsoft Query 1.0, if you attempt to create more than one outer join, you will receive the following error message:

   Query can have only one outer join

CAUSE

Microsoft Query version 1.0 is limited to one outer join between two tables (Microsoft Query can perform several inner joins and/or a single outer join in a query).

WORKAROUND

To work around this limit, you can create multiple outer joins by modifying the SQL statement containing the single outer join so that it performs multiple outer joins.

After you make the modification, Microsoft Query will not be able to display the multiple outer joins, but it will be able to display the resulting query.

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.

The following is an example of a double outer join using two related dBASE tables. In order for the example to work correctly, you must have a data source installed for the dBASE file format.

Creating Sample Databases

1. In Microsoft Excel, create the following table:

         A1: FIRSTNAME     B1: LASTNAME       C1: LETTER
         A2: John          B2: Smith          C2: a
         A3: Sam           B3: Smith          C3:b
         A4: Mike          B4: Smith          C4: c
         A5: Jack          B5: Johnson        C5: d
         A6: John          B6: Johnson        C6: e
         A7: Sam           B7: Johnson        C7: f
         A8: Mike          B8: Johnson        C8: g
   
   

2. From the File menu, choose Save As. Save the file in the dBASE file format, and name it EMPA.DBF.

3. On a new worksheet, create another table with the following information:

         A1: FIRSTNAME     B1: LASTNAME       C1: COUNT
         A2: Sam           B2: Johnson        C2: 6
         A3: Mike          B3: Johnson        C3: 7
         A4: George        B4: Smith          C4: 1
         A5: Sam           B5: Smith          C5: 2
         A6: Mike          B6: Smith          C6: 3
   
   

4. From the File menu, choose Save As. Save the file in the dBASE file format, and name EMPB.DBF.

Creating Two Outer Joins

1. Start Microsoft Query, and choose New Query from the File menu.

2. In the Select Data Source dialog box, it choose dBASE files, and choose

   Use button.

      SELECT empa.FIRSTNAME, empa.LASTNAME, empa.LETTER, empb.FIRSTNAME,
      empb.LASTNAME, empb.COUNT FROM {oj c:\wow\empa.dbf empa LEFT OUTER
      JOIN c:\wow\empb.dbf empb ON empa.FIRSTNAME = empb.FIRSTNAME}

      SELECT empa.FIRSTNAME, empa.LASTNAME, empa.LETTER, empb.FIRSTNAME,
      empb.LASTNAME, empb.COUNT FROM {oj c:\wow\empa.dbf empa LEFT OUTER
      JOIN c:\wow\empb.dbf empb ON (empa.FIRSTNAME = empb.FIRSTNAME and
      empa.LASTNAME=empb.LASTNAME.}