Create a Relationship

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

Manually Create a Relationship Between Two Tables

1. In the Query Canvas, click and drag a field from one table to the corresponding field in another table. The system draws a line indicating the joined fields. Note that you can join tables even when the data type of the associated fields is unknown (?).



2. If necessary, repeat step 1 to create additional joins to create the appropriate one-to-one or one-to-many relationship between tables.
3. Once you have defined the relationship, proceed to the next step: Select and Create a Query Field.

Modify Relationship Properties

1. In the Query Canvas, double-click the line connecting the two tables or right-click the line and select Join Properties.
2. Enable the Show Advanced Options checkbox to view additionally fields.



3. 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, you must enter a SQL command to convert the numeric field to a text field (CHAR(<field>) in the following screenshot).
Right Field	In special cases, use the Right Field section to modify the field in the right table to adhere to SQL rules.
Join Type	Select the required join type. This defaults to an inner join relationship to only include rows where the joined fields from both tables are the same. Additional options include to join all records from the left table and only those records in the right table where the joined fields match, or to join all records from the right table and only those records in the left table where the joined fields match.
Apply Trimming to Both Fields	Enable this checkbox to trim (remove leading blanks) for both of the joined fields.

4. Click OK.
5. To delete a relationship, right-click the line connecting the two tables and select Delete or press the delete key.
6. Once you have defined the relationship, proceed to the next step: Select and Create a Query Field.