Defining Relationships

If you define relationships, we recommend using a WHERE clause:

• MAINTABLE WHERE MAINTABLE.KEY_ID IN (SELECT FOREIGN_KEY FROM OTHERTABLE WHERE [THE REMAINDER OF YOUR FILTER]) AND [ANY CRITERIA AGAINST THE MAINTABLE]

Here is an example of this query method from the Work Order Module. This query will result in all of the work orders where there are work order locations with no building number defined and no X or Y Coordinate defined and a status of Complete:

• WKORDER WHERE WO_ID IN (SELECT WL_WO_ID FROM WKORDERLOC WHERE  WL_ADR_BDG IS NULL AND WL_X_COORD IS NULL AND WL_Y_COORD IS NULL) AND WO_STAT_CD < 950

  Note: When defining a relationship, you should not modify the table(s) specified in the query. Instead, you should add a WHERE clause to define which records you would like returned in the filter.