DOCUMENT:Q89181  10-AUG-1999  [foxpro]
TITLE   :Outer Join Syntax Example for SELECT-SQL Statement
PRODUCT :Microsoft FoxPro
PROD/VER:2.5x 2.6x 3.00 | 2.00 2.5x 2.6x
OPER/SYS:
KEYWORDS:kbcode

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, versions 3.0, 5.0, 6.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

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 database that have a corresponding record in
the child database. 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
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 database that have no corresponding
child record. This is done as follows:

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:

   Q119901 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.

Additional query words: VFoxMac FoxDos VFoxWin FoxWin 2.50 2.50a 2.50b kbvfp300 kbvfp500 kbvfp600 kbdatabase 2.60 2.60a

======================================================================
Keywords          : kbcode 
Technology        : kbHWMAC kbOSMAC kbVFPsearch kbAudDeveloper kbFoxproSearch kbZNotKeyword3 kbFoxPro200DOS kbVFP300bMac kbVFP300 kbVFP500 kbVFP600
Version           : 2.5x 2.6x 3.00 | 2.00 2.5x 2.6x

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1999.