Joining of tables is required when the dataset query design demands for more than one table. The following sections describes the steps required to create and edit the relationships between tables.
Refer Create Data section for better understanding with the following steps.
Click on the Join
icon in the query designer toolbar to open Query Joiner
dialog.
The Join
icon in the query designer toolbar will be in disabled state, if there is only one table found dropped in table design view like below:
The Join
icon will get enabled once the second table found dropped in table design like below:
Drag and Drop tables here
like below.
2. Drag and drop another table into the design area.
3. If the subsequent table being dropped, has any of its column as foreign key in any of the already dropped tables, the joining will take place automatically. Else, it will prompt the join editor like below to let you define the keys (columns) to join between this table and any one of the already dropped tables.
Left Table
drop-down list illustrates the list of table dropped in design area.Right Table
drop-down list illustrates the list of table dropped in design area. By default, the table which you have dropped recently will be selected in this field.In the below snap, the new relationship is initiated between SalesOrderHeader
and SalesOrderDetail
tables.
The joins are used to retrieve data from two or more data tables, based on a join condition.
Types:
Refer Supported join types section to learn about the purpose of each join type.
In the below snap, inner join
is created between the SalesOrderHeader
and SalesOrderDetail
tables.
In the below snap, the join field is created for SalesOrderID
column in both tables.
To compare the values of the two columns (one from each table) between tables, any of the operator list shown in the below image can be used.
In the below snap, the Equal
operator is applied between SalesOrderID
column of both tables.
If you want to create multiple join condition for single table relation follow the below steps:
Add Field
button to create multiple join conditions for single table relation.
2. Choose the new column field in Left Field and Right Field drop-down list, then choose the operator condition.
3. Now, a new join condition will be created like below.
2. Click on the
OK
button.
3. Now, the second table will be dropped in the design area like below.
Join
icon in the toolbar to open the Query Joiner
dialog.
2. To edit the existing join condition in a table relation, click on the
Edit
icon in the respective field.
3. Clicking on the icon will enable the respective join fields.
4. Edit the join condition as required and click on the
tick
icon to update the edited join condition.
5. Then, click on the Ok
button to save the modified join relationship.
To delete a join condition in a table relation follow the below steps.
Query Joiner
dialog.
3. Click on the above highlighted icon, to remove the join condition.
4. Click on the
tick
icon to update the join condition and then click on the Ok
button to save the table relation.
A table relation must have atleast one join condition.
Query Joiner
dialog.
3. Click on the
Ok
button to save the joiner state.
Query Joiner
dialog and click on the ADD
icon to create a new table relation.
3. Choose the new tables and join information in the fields and click on the tick icon.
4. Click on the
OK
button to save the joiner state.