When you read from multiple tables using a single statement, such as a FOR EACH or OPEN QUERY statement, the AVM returns the results as a join of the tables. A join is a binary operation that selects and combines the records from multiple tables so that each result in the results list contains a single record from each table. That is, a single join operation combines the records of one table with those of another table or combines the records of one table with the results of a previous join.
FOR EACH Table1, EACH Table2 WHERE C11 = C21, EACH Table3 WHERE C22 = C31:
DISPLAY C11 C12 C21 C22 C31 C32 WITH TITLE "Join123".
The following figure shows how you can join three tables.
Figure 3. Inner joins
A table or prior join can be either on the left- or righthand side of a join operation. Thus, the results of joining the three tables in the above figure depends on two join operations—one join between Table1 (left-hand side) and Table2 (righthand side) and one join between the first join (left-hand side) and Table3 (right-hand side). The relations C11 = C21 and C22 = C31 represent join conditions, conditions that determine how one table is related to the other (that is, which records selected from one table join with the records in the other table). How the records from joined tables are combined depends on the order of the tables in the join, the type of join operation used, and the selection criteria applied to each table.
