You can configure a Source transformation to join related objects. You can join related objects based on existing relationships or custom relationships. The types of relationships that you can create are based on the connection type.
Use the following relationships to join related objects:
Existing relationships
You can use relationships defined in the source system to join related objects. You can join objects with existing relationships for the following connection types:
Database
Salesforce
Some
Data Integration
connectors
To join related objects, you select a primary object. Then you select a related object from a list of related objects.
For example, after you add Opportunity as a primary Salesforce source object, you can add any related objects, such as Account.
The following image shows a list of Salesforce objects with existing relationships with the Opportunity object:
Custom relationships
You can create custom relationships to join objects in the same source system. To create a custom relationship, select a primary object, select another object from the source system, and then select a field from each source to use in the join condition. You must also specify the join type and join operator.
You can select one of the following join types:
Inner
Performs a normal join. Includes rows with matching join conditions. Discards all rows that do not match, based on the condition.
Left
Performs a left outer join. Includes all rows for the source to the left of the join syntax and the rows from both tables that meet the join condition. Discards the unmatched rows from the right source.
Right
Performs a right outer join. Includes all rows for the source to the right of the join syntax and the rows from both tables that meet the join condition. Discards the unmatched rows from the left source.
For example, the following image shows a custom relationship that uses an inner join to join the EMPLOYEE and MANAGER database tables when the EMPLOYEE.E_MANAGERID and MANAGER.M_ID fields match: