Join Using SELECT SQL Does Not Return Any Records

• 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

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