Join Using SELECT SQL Does Not Return Any Records

ID: Q95670

The information in this article applies to:

Microsoft FoxPro for Windows, versions 2.5 and 2.5a
Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, and 2.5a

SUMMARY

When the SELECT SQL statement is used to join two databases, the desired effect is to retrieve records that meet conditions involving the databases in the FROM clause. However, under some circumstances, the join might not return any records. The code and explanation below demonstrate this.

MORE INFORMATION

The query below demonstrates a join of the CUSTOMER.DBF and INVOICES.DBF databases in the TUTORIAL directory. This join should retrieve all the customers who are NOT in the INVOICES database:

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

The query above retrieves all the customers who do not have any invoices. However, if the INVOICES database is empty, the above query doesn't return any records even though the expected result is for every record to be returned. This effect occurs because the INVOICES database appears in the first FROM clause of the first SELECT statement.

A database that appears in a FROM clause must contain at least one record in order for the query to work correctly. Therefore, if the INVOICES database reference in the first FROM clause of the first SELECT statement is removed, all the customers who are not in the INVOICES database will be returned. This result occurs even if the INVOICES database is empty. The query below demonstrates this behavior:

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

Additional reference words: FoxDos FoxWin 2.00 2.50 2.50a KBCategory: kbprg KBSubcategory: FxprgSql

Last Reviewed: June 27, 1995