To add a new join

  1. Select the existing first (left) table to which you want to join. This might be the main table, or an existing join.
  2. Click Add Join.

    The Table Join Properties window opens.

  3. Make the following settings:

    Table

    Select the name of the second (right) table that you want to join. The drop-down list includes all of the tables from your local database model.

    Alias

    Type an alias name for the join. This becomes the "name" of the virtual table created by the join. This name can be used by other expressions in the full SQL request submitted to the database together with the join statement (such as those added as join extension expressions, see below). All joins defined for a single mapping must have unique alias names.

    Join type

    Choose the type of join you wish to make. You can choose to make a LEFT JOIN, INNER JOIN or EXIST predicate. See the icon descriptions above and/or an SQL reference manual for more information about join types.

    Distinct

    Select this to make a "distinct" join. This will ensure that only one copy of each record containing the matching value is returned. Note that this type of operation places a relatively heavy load on the database, so you should use it sparingly (if at all). It is usually best to find another way of filtering redundant records.

    Fixed

    During deployment, the code generator checks the join hierarchy for each method to see if unused tables can be removed from the generated Java class for that method. Usually this creates streamlined code with the intended functionality. However, it can sometimes strip a linking table not explicitly named elsewhere, but which is actually required (for example, by code entered in a join extension). Select Fixed to ensure that the join will always be included, provided its parent join is also included.

  4. Click OK.

    All joins must include a relation statement that identifies the primary and foreign key fields.

  5. Select your new join and then click Add Relation.

    The Select a table relation to add window opens. This window lists all of the currently defined relations that include both the left and right tables of the selected join.

  6. Select the relation that you wish to use and click Add to add it to your join.

    You then return to the Table Join Hierarchy window, which now includes an icon for your new relation nested under your selected join.

    If no relation is listed, then your data source has probably not yet been configured with one for the two tables of your join. For instructions about how to add one and then return to this procedure, see Specifying database table relations.