Configuring a relationship
The Join Editor presents a list of relationships similar to a SQL join :
Data is retrieved from a first table joined with X other tables.
This relationship expresses how an entry in Table1 will be on the same row as another entry in Table2.
Each relationship can be selected by clicking in the list :
Selecting the Join Type
A list allows to select the Join Type :
- Inner Join : only retrieve data where the join condition is true
- Left Outer Join : retrieve all data from the first table (“left table”) even if the join condition is not true. If the join condition is false and no data exists in the second table (“right table”), then NULL data is retrieved for the second table.
Join Conditions
In order to match data between tables, a condition must be expressed.
For example, if Table1 contains Customer orders and Table2 contains Persons profiles, you may want to join on “CustomerName = PersonsName”.
This means that the resulting dataset will show Persons information on the same row as Customer order information when the Name is the same.
By default a join condition is proposed in the editor.
Configuring a Join Condition
A condition is composed of 3 elements from right to left :
- a right member : a field from the currently selected Right Table. For your first join, this is the table you just added to the Data Source
- an operator : will express the relationship
- a left member : a field from any other table
To configure the Condition :
Select a right member
Select a field from the current table which will have to match the left field :
Select a left member
Select a field from any other table which will have to match the selected right field.
In the list, the fields are grouped by table names :
Select an operator
Select an operator from the list to express the desired relationship between the two selected fields :
Multiple join conditions
Sometimes a single condition is insufficient to express the desired relationship.
Adding a condition
It’s possible to add as many conditions as desired.
Conditions can be added by clicking the “Add” button :
Removing a condition
A minimum of 1 condition is required.
Each condition can be removed from the relationship by clicking on the corresponding red cross :
Post your comment on this topic.