Outer Join with Three or More Data Tables

ID: Q100069

2.00 2.50 2.50a | 2.50 2.50a 3.00

MS-DOS          | WINDOWS

The information in this article applies to:

• Microsoft Visual FoxPro for Windows, versions 3.0, 6.0
• Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, 2.5a
• Microsoft FoxPro for Windows, versions 2.5 2.5a

SUMMARY

When two data tables are joined through a SELECT-SQL statement, only those records that have entries in both the parent and child databases are included in the resulting data table. If it is necessary to also include parent records that do not have corresponding child entries, an outer join is performed.

A SQL outer join creates one data table that includes parent records--both those with and those without corresponding child records--and fields from both the parent and child databases.

Using an outer join, you can create one final result set from three or more databases (where the second and subsequent databases are each children of the parent) that includes fields from all databases regardless of whether the parent has records in any one or more of the child databases. The procedure for creating this single result set is detailed below.

MORE INFORMATION

To create one result set from three databases, where one database is the parent and the two other databases are children of the same parent, do the following:

1. Using the CUSTOMER database located in the GOODIES\TUTORIAL subdirectory

   in the main FoxPro directory or in the SAMPLES\DATA directory in Visual
   FoxPro, create the following sample data set:

      USE customer
      COPY TO child1 NEXT 100
      SKIP -10
      COPY TO child2 NEXT 50

   The parent file will now have 100 children in CHILD1 and 50 different
   children in CHILD2. For several customer numbers, child records will
   exist both in CHILD1 and CHILD2.

3. Perform an outer join between the parent (CUSTOMER) and the first child

   (CHILD1). Store the results in a cursor so you don't have to eliminate
   temporary files later.

      SELECT customer.cno, child1.company ;
          FROM customer, child1 ;
          WHERE customer.cno = child1.cno;
       UNION ;
       SELECT customer.cno, "" FROM customer ;
          WHERE customer.cno not in ;
          (SELECT child1.cno FROM child1 ;
          WHERE customer.cno = child1.cno ) ;
          INTO CURSOR oj1

   NOTE: The placeholder for CHILD1.COMPANY is critical, since this field
   would otherwise be blank whenever there was not a match between the
   parent and child. This would result in a huge output data set, since an
   attempt would be made to join each blank CHILD1.COMPANY field with each
   CUSTOMER.CNO record. Use a placeholder for any field in the child
   database that was selected in the first half of the outer join command.

      SELECT customer.cno, child2.contact, child2.lat ;
          FROM customer, child2 ;
          WHERE customer.cno = child2.cno ;
       UNION ;
       SELECT customer.cno, "", 0 FROM customer;
          WHERE customer.cno not in ;
          (SELECT child2.cno FROM child2 ;
          WHERE customer.cno = child2.cno ) ;
          INTO CURSOR oj2

   the number of available file handles, open work areas, free disk space,
   and RAM.)

   steps 3 through 5:

      SELECT * ;
         FROM oj1,oj2 ;
         WHERE oj1.cno = oj2.cno ;
         INTO CURSOR ojfinal

   with each child:

      SELECT child1
      USE
      SELECT child2
      USE

      SELECT ojfinal
      BROWSE

REFERENCES

For more information, see "SELECT-SQL" in the FoxPro 2.0 "Commands & Functions" manual or in the FoxPro 2.5 "Language Reference" manual, or please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q89181
   TITLE     : Outer Join Syntax Example for SELECT-SQL Statement

Keywords          : FxprgSql 
Version           : 2.00 2.50 2.50a | 2.50 2.50a 3.0
Platform          : MS-DOS WINDOWS

Last Reviewed: October 23, 1998