BUG: SELECT-SQL Error When Joining Parent to Two Child Tables

• Microsoft Visual FoxPro for Windows, versions 5.0, 5.0a

SYMPTOMS

Using the SELECT-SQL command with the ON clause, to Join a Parent table to two child tables, may cause the following error:

   SQL: Column <column name> is not found

CAUSE

The Visual FoxPro query parser looks at the SELECT-SQL queries starting from the innermost part of it. In the case of the SELECT..JOIN..ON statement in the MORE INFORMATION section of this article the parser looks at the following:

   INNER JOIN test3 ;
      ON  test1.t1f1 = test3.t3f1 ;

WORKAROUND

You can use one of the following workarounds to correct this behavior:

1. Run the Query after opening all the tables involved with the query. This way the Visual FoxPro query parser is able to resolve all the references.

2. Modify the Query in the MORE INFORMATION section of this article as follows:

         SELECT *;
           FROM test1;
           INNER JOIN test2;
             ON  test1.t1f1 = test2.t2f1 ;
           INNER JOIN test3 ;
             ON  test1.t1f1 = test3.t3f1
   
   

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create and Populate three tables as following:

         CREATE TABLE test1 (t1f1 c(5), t1f2 c(5))
         INSERT INTO test1 VALUES("AAAAA", "11111")
         INSERT INTO test1 VALUES("BBBBB", "22222")
   

         CREATE TABLE test2 (t2f1 c(5), t2f2 c(5))
         INSERT INTO test2 VALUES("AAAAA", "A1A1")
         INSERT INTO test2 VALUES("AAAAA", "A2A2")
   

         CREATE TABLE test3 (t3f1 c(5), t3f2 c(5))
         INSERT INTO test3 VALUES("BBBBB", "B1B1")
         INSERT INTO test3 VALUES("BBBBB", "B2B2")
   

2. Issue the "CLOSE ALL" command in the Command window to Close all the tables.

3. Issue the following SQL Statement:

         SELECT *;
           FROM  test1;
           INNER JOIN test2;
              INNER JOIN test3 ;
              ON  test1.t1f1 = test3.t3f1 ;
           ON test1.t1f1 = test2.t2f1
   
   

When creating a query to Join a Parent table to two child tables using the View Designer, the SQL statement that is built is similar to the one in step 3. Because of this, the View Designer is not able to process the query correctly.

The above article discusses the correct syntax for a select based on a parent, child and grandchild relationship.

REFERENCES

For more information, please see the following article in the Microsoft Knowledge Base: