Adding Join Conditions
A join works by combining items of two or more structures according to a condition, so a join always requires at least one condition. There are several ways to add join conditions, as shown below.
| Note: | When database tables are joined in SQL mode, MapForce will create the join condition (or conditions) automatically, based on foreign key relationships detected between tables. For automatic join conditions to happen, the database tables must be in a child-parent relationship on the MapForce component (that is, one table must be "parent" or "child" of another one on the component), see Example: Join Tables in SQL Mode. |
Approach 1: Add a join condition from the component properties
1.On the mapping, make sure that at least two structures (or database tables) are connected to the Join component. The Join component illustrated in this example is part of the JoinPeopleInfo.mfd mapping available in the folder <Documents>\Altova\MapForce2024\MapForceExamples\Tutorial\. This mapping is discussed in more detail in Example: Join XML Structures.
2.On the Join component, click the Define Join Condition ( 
) button (or right-click the header of the component, and select Properties from the context menu).
3.Select an item from the left structure and another one from the right structure (that is, whenever the comparison of this pair returns true, the left and right structures become joined).
If you need to add multiple conditions, click Add Condition, and then select a new pair of items. For example, in the image above, two join conditions are defined:
1.FirstName in the Structure 1 must be equal to FirstName in Structure 2, and
2.LastName in Structure 1 must be equal to LastName in Structure 2.
To remove a join condition, click the Delete 
button next to it.
Notes:
•When multiple join conditions exist, all of them must be satisfied in order for the two structures to be joined. In other words, multiple conditions are joined by a logical AND operation. This also includes optional conditions that were added from the mapping (see Approach 2 below).
•If more than two structures are connected to the Join component, such additional structures appear in the drop-down list below "Structure 2". When you select such an additional structure from the drop-down list, the left pane displays all structures that occur before it on the Join component. This way you can define join conditions between any of the multiple structures. For an example, see Example: Create CSV Report from Multiple Tables.
•To view the data type of items in each structure, select the Show types check box. The Show annotations option displays additional information about items, provided that such information exists in the underlying schema (or database). If both check boxes are selected, the layout changes to accommodate the display of both annotations and types, for example:
Approach 2: Add a join condition from the mapping
•On the mapping, add components which produce a Boolean value, and then connect the Boolean output to the input of the condition item. For example, the equal function may compare a value with some mapping item, and supply the Boolean result as input to the condition item of the join component.
| Note: | If no condition is defined from the join component properties (Approach 1), the condition item of the join component must be connected (Approach 2). |
Approach 3: Mixed approach
In the same mapping, it is possible to define some join conditions in component properties (Approach 1) and combine them with the one from the mapping (Approach 2). However, if you intend to join database tables in SQL mode, the conditions must be defined strictly using Approach 1, see also Joins in SQL Mode.