How to use a program to perform an outer join that preserves all records in the parent table and processes the nested SELECT-SQL statements in FoxPro (89181)

MORE INFORMATION

The code below demonstrates an outer join of the CUSTOMER.DBF and INVOICES.DBF tables 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.CUST_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
				

The lines from the first SELECT statement to just before the UNION statement

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

select all the records in the parent table that have a corresponding record in the child table. This is what you normally get when you do a join. The UNION statement tells FoxPro to include the output of the first half of this SELECT-SQL statement with the second half in the final results. It is important that the data in the second half of the query have the same structure as the data in the first half of the query, otherwise errors are generated.

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) ;
				

retrieve all of the records in the parent tables that have no corresponding child record. This is done as follows:

1. The SELECT-SQL 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-SQL 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-SQL 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:

   119901 How to Use the UNION Clause in a FoxPro SELECT Statement

If the ORDER BY clause is entered at the end of the SELECT statement, and the CNO field is used, an error message occurs. For example, the following clause

   ORDER BY cno
				

results in an "Fieldname is not unique and must be qualified" error message. Similarly, the following clause

   ORDER BY customer.cno
				

results in an "SQL Invalid Order by" error message.

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

   ORDER BY 1
				

results in a query ordered by CNO since CNO is in column 1 of the output table.