Join rule

To create more-advanced queries and reports that require data entries from more than one table, you must join the necessary tables together.

For example, EQ_MAIN contains equipment units' department codes, but not department codes' names or addresses; DPT_MAIN contains all detailed department data by department code. To include all the data items on a report, you must join EQ_MAIN to DPT_MAIN. A specification of the column (or columns) to use in order to connect data in one table to data in another table is called a join rule.

Join rules are defined on the Restrictions tab of the Queries/Reports -> Query/Report Generator screen. When you construct a join rule, always specify both the table name and column name for each column you name. The format is:

TABLE_B.column_name = TABLE_A.column_name

The table and column names are not case sensitive. The system, however, always names tables in uppercase characters and columns in lowercase characters (except the first segment of column names, which can be used to join to other tables, and which the system names in uppercase).

For more information, refer to the following join rule examples:

• Example 1 - A report lists departments and shows the account ID for each department. The account name should also be on the report.

• Example 2 - A report lists equipment units and shows the assigned repair shop name and department name for each equipment unit. The report should also show the account name for the account ID assigned to each department (not the account ID assigned to the equipment unit).

The system database contains two types of objects (parts and work orders) that are identified by more than one value. When you join tables together based on a part number or a work order ID, you must specify multi-column join rules. Examples 3 and 4 demonstrate how to join on part number and work order ID.

• Example 3 - A report lists parts stocked at a particular location, and shows the keyword and short description for each part. It obtains the list of parts from one table and the keyword and short description for each part from another table; the two tables must be joined on part number.

• Example 4 - A report lists parts that have been issued to work orders that are currently open. It obtains the list of parts from one table and the status of each work order from another table; the two tables must be joined on work order ID.