Create a Relationship

When a table is selected in a query, the Auto Join option automatically creates a relationship or join between the tables, based on the database architecture. This article describes how to create and modify table relationships.

Manually Create a Relationship Between Two Tables

1. In Query Canvas, click and drag a field from one table to the corresponding field in another table. The system draws a line, indicating which fields are joined.

2. If necessary, repeat step 1 to create additional joins, which may be needed to create the appropriate one-to-one or one-to-many relationship between tables.
3. Once the relationship is properly defined, proceed to the step: Select and Create a Query Field.

Modify Relationship Properties

1. In Query Canvas, double-click the line connecting the two tables, or, select the relationship line and right-click. The Join Properties panel appears. The Advanced Options are only visible if the Show Advanced Options check box is selected.

2. Use the following table to enter data in the Join Properties panel:

Field	Description
Left Field	In special cases, use the Left Field section to modify the field in the left table, to adhere to SQL rules. For example, to join a text field to a numeric field, a SQL command must be entered to convert the numeric field to a text field (CHAR(<field>) in the below screenshot):
Right Field	In special cases, use the Right Table section to modify the field in the right table, to adhere to SQL rules.
Join Type	Specify the appropriate join type. By default, an inner join relationship is selected, including only rows where the joined fields from both tables are the same. Additional options are available to join all records from the left table, and only records in the right table, where the joined fields match, or to join all records from the right table, and only records in the left table, where the joined fields match.
Apply Trimming to Both Fields	If selected, the system trims (removes leading blanks) for both of the joined fields.

3. Once all modifications are complete, click OK.
4. To delete the a relationship, right-click the line connecting the two tables, and select Delete, or press the Delete key.
5. Once the relationship is properly defined, proceed to the step: Select and Create a Query Field