A record can have multiple property values for a given property name. For example, a record could have two values for the property Id.
If a record is configured to join to another record based on a key that has multiple values in one or both of the records, the join implementation must consider the multiple values in the comparison.
The question is, if the record has the values {A, B} for the property Id, should it match to records with value A, value B, or both? The answer is that the record matches to records that have exactly both values. This behavior is different than the semantics of a database join, because tuples in a database have only one value per column. Therefore, you should carefully consider how to handle records that have multiple values per key component.
Note
This section describes how to deal with records that have multiple values per join key. Do not confuse this scenario with one where your join keys incorporate multiple properties/dimensions.
The following example illustrates the effects of joining records that have multiple values for a join key.
A left join, using Id as the join key, on these two data sources results in the following:
The record from Source 1 with join key (Id=A, Id=BB) is combined with a record with the same key from Source 2. Similarly, since both sources have a record with keys (Id=A, Id=CC) and (Id=B, Id=CC), these records are combined appropriately. Finally, the record (Id=DD, Id=A) from Source 1 is combined with the record (Id=A, Id=DD) from Source 2. The order of the property values is not significant.
You can tweak left joins in which the left source has multiple values for a key by telling Forge to create a separate join key based on each value.
