In 4D it is possible for two tables to have multiple relation paths between them. Here is an example:



This example illustrates the following relations:

• Employees reside in Cities.
  
• Companies are based in Cities.

The question is, what happens when the following query is performed?

QUERY([Emp];[City]Name="San Jose")

Another way to ask the question: are we querying for Employees that live in "San Jose" or Employees that work in "San Jose"?

In 4D 2004 (and previous versions) the first relation path encountered is the one that is followed. In this case "first" means field order. So in 4D 2004 the query looks for Employees that work in "San Jose" because the query passes through the Company table.

In 4D v11 SQL the shortest relation path is always used. So in 4D 11 the query looks for Employees that live in "San Jose".

It is important to understand this change when converting databases to 4D v11 SQL because query results may change through no fault of the developer.

Finally please note that you have complete control over the relation path followed with the QUERY BY FORMULA command in 4D v11 SQL. Please see this Tech Tip for more information.