Using Conditional Joins

How to:

Using conditional joins, you can establish joins based on conditions other than equality between fields. In addition, the host and cross-referenced join fields do not have to contain matching formats and the cross-referenced field does not have to be indexed.

The conditional join is supported for FOCUS and all relational data adapters. Because each data source differs in its ability to handle complex conditional criteria, the optimization of the WHERE syntax differs depending on the specific data sources involved in the join and the complexity of the conditional criteria.

To display a list of joined data sources, select the following query subject in the Query tool or issue the command from the Command Console:


This displays every join currently in effect and indicates any that are based on conditional criteria.

Top of page

Procedure: How to Create Conditional Joins
  1. Click the Add File button and select the tables you want to use.
  2. Drag common fields between the tables to establish a join.
  3. Select the join (the connector line) and click the Create/Edit Selection icon from the toolbar. The WHERE Expression Builder appears, as shown in the following image.

  4. Click OK to return to the Join tool after you have specified your WHERE criteria.

    If you need to change the join type or join name, double-click the conditional join to launch the Join Properties window.