Join Tab, Query and View Designers

  • Article

Specifies join conditions for matching and selecting records in one or more tables or views, such as specific values in a field or join conditions defining temporary relationships between tables. This tab determines the join conditions listed in the FROM clause of the SELECT - SQL Command in the SQL window. The Filter tab appears in the Query and View Designers.

  • 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.
  • Type
    Specifies the type of join condition. By default, the join type is inner. When creating a new join condition, click the field to display a drop-down list of the join types.
    • Inner   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.
    • Right Outer   Specifies that records that match the join condition, and records from the table on the right of the join condition that do not match, are included in the results.
    • Left Outer   Specifies that records that match the join condition, and records from the table on the left of the join condition that do not match, are included in the results.
    • Full Join   Specifies that records that match and records that do not match the join condition are included in the results. The field must match the example text, character for character.
  • 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.
  • Not
    Reverses the condition to exclude records matching the condition.
  • Criteria
    Specifies the type of comparison. The comparison types are:
    • Equal   Specifies that the fields have the same value.
    • Like   Specifies that the field must include characters that match characters in the example text. (Customer.state Like O would match records from Ohio, Oklahoma, and Oregon.)
    • Not Like   Specifies that the field must not include the characters in the example text. (Customer.state Not Like O would not match records from Ohio, Oklahoma, and Oregon.)
    • Exactly Like(==)   Specifies that the field must match the example text, character for character.
    • Not Exactly Like(Not ==)   Specifies that the field must not match the example text, character for character.
    • Greater Than (>)   Specifies that the field must be the same or more than the value in the example text.
    • Greater Than or Equal To (>=)   Specifies that the field must be more than the value in the example text.
    • Less Than (<)   Specifies that the field must be the same or less than the value in the example text.
    • Less Than or Equal To (<=)   Specifies that the field must be less than the value in the example text.
    • Is NULL   Specifies that the field must contain a null value.
    • Is Not NULL   Specifies that the field must not contain a null 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 example text. The two values in the example text are comma-delimited. (Invoices.idate Between 05/10/97,05/12/97 would match records for the 10th, 11th, and 12th of May, 1997).
    • Not Between   Specifies that the field must not be greater than or equal to the lower value, and not less than or equal to the higher value, in the example text. The two values in the example text are comma-delineated. (Invoices.idate Not Between 05/10/97,05/12/97 would not match records for the 10th, 11th, and 12th of May, 1997).
    • In   Specifies that the field must match one of several comma-delineated examples in the example text.
    • Not In   Specifies that the field must match one of several comma-delineated examples in the example text.
  • Value
    Specifies the other table and field in the join condition.
  • Logical
    Adds an AND or OR condition to the join condition list.
  • Insert button
    Inserts a blank join condition above the selected condition.
  • Remove button
    Removes the selected condition from the query.

See Also

Fields Tab, Query and View Designers | Group By Tab, Query and View Designers | Join Condition Dialog Box | Order By Tab, Query and View Designers | Query and View Designers | SELECT - SQL Command | Update Criteria Tab, View Designer | Filter Tab, Query and View Designers | Querying Multiple Tables and Views