Using the Advanced Join Dialog

The Advanced Join feature of the Query Tool allows you to perform more complex join operations rather than the common INNER JOIN relationship. It also permits you to create, change, add or delete a join relationship. To realize these functions, launch the Join Dialog by either

  1. Pressing the Advanced Joins button on the tool bar, or
  2. Right-clicking on a relationship link in the Query tab to invoke a pop-up menu, and then select Advanced Joins.

Launch Dialog

Here is the Join Dialog:

Join Dialog

This dialog comprises a left and right panel. The left panel displays the currently selected tables in the query. Those tables that have a relationship are shown joined together. The right panel shows the tables that have been selected from the left panel together with the relationship type between their joined columns. Under the Join type, there are options to choose which type of join you wish to perform, and below is the Join Relationships section where you can edit, insert or delete a join relationship as you wish.

Here are the tasks you can perform on the Join Dialog:

 

Performing an Advanced Join Operation

Here are the available join operations:

INNER JOIN

This is the most common join type, and is the default relationship provided when you create a join. This relationship will include only those matching records from both tables, that is, those records having a common field where their values correspond. To achieve this:

  1. Selecting the first option under Join Type.

    Inner Join option

  2. Press the OK button to establish the relationship.

 

LEFT JOIN

This relationship will include all records from the table on the left as well as only those matching records from the table on the right. To achieve this:

  1. Select the second option under Join Type.

    Left Join Option

  2. Press the OK button to establish the relationship.

 

RIGHT JOIN

This relationship will include only those matching records from the table on the left as well as all records from the table on the right. To achieve this:

  1. Select the third option under Join Type.

    Right Join Option

  2. Press the OK button to establish the relationship.

 

FULL JOIN

This relationship will include all records from both tables. To achieve this:

  1. Select the fourth option under Join Type.

    Full Join Option

  2. Press the OK button to establish the relationship.

 

CROSS JOIN

This relationship has no connection between the records from both tables. Therefore, each record from the table on the left will be associated with all records from the table on the right, thereby creating a one-to-many relationship, or a 'cross-product' between the tables. To achieve this:

  1. Click on the correlation text under Join Relationships.
  2. Press the Delete button to remove the association.

    Cross Join Option

  3. Repeat steps 1 and 2 if there are multiple correlations between the two tables, until there is absolutely no relationship between them.
  4. Press the OK button to establish the deletion.

 

Creating a Join Relationship

You can also use the Edit Join Dialog to create a join relationship between tables if there is none existing. To achieve this, follow these steps:

  1. Launch the Join Dialog.
  2. Click on the second table that you intend to join to the first. Ensure that both tables' names appear on the right panel.
  3. Click on the Insert button under Join Relationship to instigate the Expression Builder Dialog.
  4. Proceed to type the relationship you wish to build, by indicating the columns to join from both tables. For example, "EMPINFO.IDNUM" = "SALARY.IDNUM".
  5. Click the OK button to save this expression. Your new relationship now appears in the text area.
  6. Press the OK button on the Join Dialog to establish the join relationship.

 

Editing a Join Relationship

To edit an existing relationship, follow these steps:

  1. Launch the Join Dialog.
  2. Click on either table that holds the relationship you wish to edit.
  3. Click on the correlation text under Join Relationships.
  4. Press the Edit button to instigate the Expression Builder Dialog.
  5. Proceed to change the relationship.
  6. Click the OK button to save this expression. You can now see your change in the text area.
  7. Press the OK button on the Join Dialog to validate the change.

 

Inserting a Join Relationship

To insert a relationship, follow these steps:

  1. Launch the dialog.
  2. Click on the set of tables you wish to add your relationship to.
  3. Press the Insert button to instigate the Expression Builder Dialog.
  4. Proceed to type your new relationship, specifying the columns to join.
  5. Click the OK button to save this expression. The new added relationship now appears in the text area.
  6. Press the OK button on the Join Dialog to establish the relationship.

 

Deleting a Join Relationship

To delete a relationship, follow these steps:

  1. Launch the dialog.
  2. Click on the set of tables possessing the relationship.
  3. On the text area under Join Relationships, click on the relationship you wish to remove.
  4. Click the Delete button. That relationship is now removed from the text area.
  5. Press the OK button to save the deletion.