Query Builder Options dialog box

From the Query Builder Options dialog box, you can select how you want to automatically define joins while building your queries.

Accessing the Query Builder Options dialog box

From the Query Builder Editor, you can access the Query Builder Options dialog box by selecting Options from the Options menu.

Automatic join options

Joins can be automatically defined when tables are added to your query. You have the following options for automatically defining joins:

• Do not automatically define joins: By default, Query Builder does not automatically join columns between tables. If you keep this default, you must manually define all of the joins between tables added to your query.

• Automatically define joins based on foreign key constraints: You can configure Query Builder to automatically join columns based on your database tables' primary key and foreign key constraints. When this option is selected, primary key columns are automatically joined with their associated foreign key columns.

  For example, you have two lookup tables in your database. One table is named LU_CATEGORY for the Category attribute and one table is named LU_SUBCATEG for the Subcategory attribute. You define the CATEGORY_ID column as the primary key for the LU_CATEGORY table. The LU_SUBCATEG table includes its own primary key SUBCAT_ID and the foreign key CATEGORY_ID. When you add the tables LU_CATEGORY and LU_SUBCATEG, a join is automatically created on CATEGORY_ID to join the two tables.

• Include joins based on foreign keys when referenced table is not in the query: If you select to automatically define joins based on foreign key constraints, you also have the option to automatically create joins based on foreign keys when the table containing the primary key is not in the query. When this option is selected, the same foreign key columns in different tables are automatically joined even if the table that defines the column as a primary key is not in the query. However, the column must be defined as a primary key in at least one table in the database.

  For example, recall the example scenario above. Along with the tables LU_CATEGORY and LU_SUBCATEG you have a lookup table named LU_ITEM for the attribute Item. The LU_ITEM table includes CATEGORY_ID as a foreign key. If you add LU_SUBCATEG and LU_ITEM to your query without adding LU_CATEGORY, a join is automatically created on CATEGORY_ID to join the two tables. Since no table is included in the query that defines CATEGORY_ID as a primary key, this join is created by searching the database tables for relevant primary keys. Once it is determined that CATEGORY_ID is a primary key, the join between LU_SUBCATEG and LU_ITEM on CATEGORY_ID as a foreign key is created. If LU_CATEGORY is included before or after the other two tables, the joins are created based on CATEGORY_ID as a primary key and any redundant joins are deleted.

• Automatically define joins based on column names and data types: You can configure Query Builder to automatically join columns based on column names and data types. When this option is selected, columns are automatically joined by matching columns by name and data type.

  If primary key columns are included in at least one of the tables of your query, joins are only automatically created between the primary key columns and any columns that match the primary key columns' column names and data types.

• Display foreign key relationships with dotted lines: This check box allows you to show and hide the display of dotted lines between tables to indicate foreign key relationships. Select the check box to show the dotted lines.

For more information on how the different automatic join options function, refer to the Advanced Reporting Help.

Related Topics

• Join Editor

• Creating a join

• Modifying a join

• Removing a join