Outer Join Syntax Example for SELECT-SQL Statement

• Microsoft Visual FoxPro for Windows, version 3.0
• Microsoft FoxPro for Windows, versions 2.5x, 2.6x
• Microsoft FoxPro for MS-DOS, versions 2.0, 2.5x, 2.6x
• Microsoft Visual FoxPro for Macintosh, version 3.0b

SUMMARY

When FoxPro Relational Query By Example (RQBE) is used to join two databases, a record in the parent database that does not have any corresponding record in the child database is omitted from the joined database. However, under some circumstances, this is not the desired behavior. The text below details how to perform an "outer join" that preserves all records in the parent database.

NOTE: The RQBE interface cannot perform an outer join; a program must perform the join because the RQBE interface cannot process the necessary nested SELECT-SQL statements.

MORE INFORMATION

The code below demonstrates an outer join of the CUSTOMER.DBF and INVOICES.DBF databases in the FOXPRO2\TUTORIAL directory.

NOTE: In Visual FoxPro for Windows, open the CUSTOMER.DBF and ORDERS.DBF files in the VFP\SAMPLES\MAINSAMP\DATA subdirectory. Then, substitute CUSTOMER.COMPANY_NAME for CUSTOMER.COMPANY, CUSTOMER.CUSTOMER_ID for CUSTOMER.CNO, and ORDERS.ORDER_ID for INVOICES.INO in the code below.

(Note that the space before each semicolon [;] character is required. Omitting the space from the query causes an error.)

   SELECT customer.company, customer.cno, invoices.ino ;
      FROM customer, invoices ;
      WHERE customer.cno = invoices.cno ;
   UNION ;
   SELECT customer.company, customer.cno, 0 ;
      FROM customer ;
      WHERE customer.cno NOT IN ;
         (SELECT invoices.cno FROM invoices ;
            WHERE customer.cno = invoices.cno) ;
   INTO CURSOR QUERY

   SELECT customer.company, customer.cno, invoices.ino ;
      FROM customer, invoices ;
      WHERE customer.cno = invoices.cno ;

The lines after the UNION statement

   SELECT customer.company, customer.cno, 0 ;
      FROM customer ;
      WHERE customer.cno NOT IN ;
         (SELECT invoices.cno FROM invoices ;
            WHERE customer.cno = invoices.cno) ;

1. The SELECT statement in the parentheses creates a list of all the records that have corresponding child records.

         (SELECT invoices.cno FROM invoices ;
            WHERE customer.cno = invoices.cno) ;
   
   

2. The SELECT statement directly above that then selects all the records that are not in the list created by step 1.

         SELECT customer.company, customer.cno, 0 ;
            FROM customer ;
            WHERE customer.cno NOT IN ;
   
   

3. The 0 in the line for the second SELECT statement is used as a placeholder. If INVOICES.INO is a date field, use { / / } instead of the 0. Similarly, if INVOICES.INO is a character field, use " ". The idea is for the placeholder to be the same type as the field that it replaces.

   Please note that at this time, there are no plans to implement the functionality for a memo field placeholder. However, a workaround for this situation is documented in the following article in the Microsoft Knowledge Base:

         ARTICLE-ID: Q119901
         TITLE     : How to Use the UNION Clause in a FoxPro SELECT
                     Statement
   
   

   ORDER BY cno

   ORDER BY customer.cno

To eliminate these errors, use the ORDER BY clause with a column number. For example, the following clause

   ORDER BY 1