Join Tab, Query and View Designers

Specifies join conditions for matching and selecting records in one or more tables or views, for example, specific values in a field or join conditions that define temporary relationships between tables. This tab appears in the Query and View Designers.

Settings in the Join tab determine the join conditions that appear in the FROM clause of the SELECT - SQL Command that is generated and that appears in the SQL window. The View Designer loads and generates any FROM clause with or without the JOIN option, using parentheses where needed in the ON clause.

• Condition Button
  Appears if two or more tables are joined in the query. Click the double-headed horizontal arrow to edit the selected condition or query criterion in the Join Condition Dialog Box, which will update entries in the other columns.

• Left Table
  Contains the alias of the left table in the join or "<Prev join>", which means the left table in the join is actually the previous subjoin.

• Join type
  Specifies the type of join condition. By default, the join type is Inner Join. When creating a new join condition, click the field to display a drop-down list of the join types.

  • Inner Join   Specifies that only the records that match the join condition are included in the results. This type is the default and most commonly used type of join.

  • Left Outer Join   Specifies that all the records in the left table, regardless whether they match the join condition, and only those records in the right table that match the join condition, are included with the results.

  • Right Outer Join   Specifies that all the records in the right table, regardless whether they match the join condition, and only those records in the left table that match the join condition, are included with the results.

  • Full Join   Specifies that both records that match and do not match the join condition are included in the results. The field must match the example text, character for character.

  • Cross Join   Specifies that each record in the left table is matched with all records in an existing right table. When a WHERE clause does not exist, setting a cross join produces a Cartesian product result set. A Cartesian product contains the set of all possible combinations of each element in a set with all elements in another set.

    The Cross Join option makes it possible to modify the table sequence appearing in the FROM clause of the SQL SELECT statement in the SQL window for the query or view. Selecting Cross Join inserts a comma (,) that separates tables in the FROM clause.

    Note

    You can specify a cross join only for the left table, which cannot be a previous join (<Prev join>). You cannot specify a cross join when adding new tables to the query or view, so if you want to set a specific sequential position for the table, you must manually add the cross join for the table.

    Outside the Query and View designers, cross joins are not supported in Visual FoxPro SQL SELECT command syntax. Cross joins are supported only for modifying the sequence of tables in Query and View Designers.

    When parsing the SQL SELECT statement, Visual FoxPro puts every table from the FROM clause that is not involved in the join into the Join tab. A row with "<alias> Cross join" appears in the join list. The sequence of tables in the FROM clause of the SQL SELECT statement matches the sequence of joins in the Join tab. When generating queries, Visual FoxPro uses this information to place a table in the correct position as specified by the FROM clause. When changes are reloaded in the Query and View designers, they are updated in the Join tab.

    If a table is not referenced in the Join tab, Visual FoxPro places it at the beginning of the FROM clause. If several unreferenced tables exist, all such tables are placed randomly at the beginning of the FROM clause.

• Pri. (Priority)
  Specifies a number ranging from 0 to 99, which indicates a priority for the join operation. The number 0 specifies the highest priority, while the number 99 specifies the lowest priority.

  Setting priorities for join operations determines the order in which joins are performed and uses parentheses (()) to group operations. For example, suppose you specify a priority of 0 for a join operation, a priority of 1 for a second join operation, and a priority of 0 for a third join operation. The first and third join operations, which have a priority of 0, are performed before the second join operation, which has a priority of 1.

• Right Table
  Contains the alias of the right table in the join or "<Next join>", which means the right table in the join is actually the next subjoin.

• Field Name
  Specifies the first field of the join condition. When creating a new join condition, click the field to display a drop-down list of the available fields. You can also specify an expression.

• Not
  Reverses the condition to exclude records matching the condition.

• Criteria
  Specifies a condition operator:

  • Equal (=)   Specifies that field and the value in the Example field have the same value.

  • Like   Specifies that the field must include characters that match characters in the text in the Example field.

    For example, Customer.state Like O matches records from Ohio, Oklahoma, and Oregon.

  • Exactly Like (==)   Specifies that the field must match, character for character, the text in the Example field.

  • Greater Than (>)   Specifies that the field must be more than the value in the Example field.

  • Less Than (<)   Specifies that the field must be the same or less than the value in the Example field.

  • Greater Than or Equal To (>=)   Specifies that the field must be the same or more than the value in the Example field.

  • Less Than or Equal To (<=)   Specifies that the field must be the same or less than the value in the Example field.

  • Is NULL   Specifies that the field must contain a null value.

  • Is True   Specifies the left field or expression evaluates to True, assuming the field or expression evaluates to a logical value.

  • Between   Specifies that the field must be greater than or equal to the lower value, and less than or equal to the higher value, in the condition appearing in the Example column. You must separate the two values in the Example field with the word AND. Visual FoxPro creates the query with the word BETWEEN.

    For example, Invoices.idate Between 05/10/97 AND 05/12/97 matches records for the 10th, 11th, and 12th of May, 1997.

  • In   Specifies that the field must match one of several values in the comma-separated list appearing in the Example field.

    For example, Customer.name In Al,George,Mary matches records in which the customer's name is Al, George, or Mary.

• Value
  Specifies the other table and field in the join condition. You can also specify an expression.

• Logical
  Adds an AND or OR condition to the list of join conditions.

• Pri. (Priority)
  Specifies a number ranging from 0 to 99, which indicates the priority for logical operations. The number 0 indicates the highest priority, while the number 99 indicates the lowest priority.

  Setting priorities for logical operations determines the order in which logical operations are performed and uses parentheses (()) for grouping operations. For example, suppose you specify a priority of 0 for a logical operation, a priority of 1 for a second logical operation, and a priority of 0 for a third logical operation. The first and third logical operations, which have a priority of 0, are performed before the second logical operation, which has a priority of 1.

• Insert button
  Inserts a blank join condition above the selected condition.

• Remove button
  Removes the selected condition from the query.

See Also

Tasks

How to: Query Multiple Tables and Views

Concepts

Join Conditions for Tables, Queries, and Views

Reference

Fields Tab, Query and View Designers

Group By Tab, Query and View Designers

Order By Tab, Query and View Designers

Update Criteria Tab, View Designer

Filter Tab, Query and View Designers