Operators for Joins

The general construction of a JOIN is:

The most straight-forward relationship between two object classes is one in which an object class property with the data type Reference or ReferenceArray references another object class. To specify that relation, you must define the JOIN as follows:

or:

For example, the JOIN

  • LeftJoin Application ON BusinessProcess.Applications = Application.REFSTR

adds all applications assigned to the business processes with the property Applications to the search result of the Alfabet query.

The ability to build a JOIN based on properties of the type ReferenceArray depends on the attribute Reference Support of the property:

  • If Reference Support is set to True , the relational operator "=" is used in the expression. It is used in the JOIN as "includes" or "is included in" rather than "is equal to" operator.
  • If Reference Support is set to False , you cannot build a JOIN on the property.

JOIN s can also specify other relations between two objects either instead of or in addition to a direct reference by a property of the type Reference or ReferenceArray . Any property of the joined object class can have a relation with any other property of the base class or an already joined class in the Alfabet query: .

For example, the JOIN

  • FIND Application
  • InnerJoin ApplicationGroup ON ApplicationGroup.ResponsibleUser = Application.ResponsibleUser

finds all combinations of application groups and applications for that the same user is defined as responsible user.

In most cases, this type of comparison is useful in combination with a JOIN condition based on a direct reference. In the example, it is probable that the user wants to make sure that the person that is responsible for an application group is a supervisor that is not identical with the person responsible for any individual applications in the application group. The information about the responsible user, that has nothing to do with the relation between application group and applications itself, also combines application groups with applications that are not assigned to that application group. A second JOIN condition must be defined that limits the search results to applications that are assigned to the application group. Detailed information about a join with multiple condition is given in a separate section..

JOIN conditions allow the use of the following operators:

Operator Description
= Equal to
< Lesser than
<= Lesser than or equal to
> Greater than
>= Greater than or equal to
<> Not equal to