Transforming data - Join Tables

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.

Open query joiner dialog

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:

Create a table relation

1. Drag a table or view from the left pane and drop it into the center pane labeled with 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.

Table drop-down list

• Left Table - The Left Table drop-down list illustrates the list of table dropped in design area.

• Right Table - The 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.

Join Types

The joins are used to retrieve data from two or more data tables, based on a join condition.

Types:

1. Inner Join

2. Left Outer

3. Right Outer

4. Full Outer.

In the below snap, inner join is created between the SalesOrderHeader and SalesOrderDetail tables.

Table fields

• Left field - The available columns of the table selected in the Left Table drop-down list will be listed here.

• Right field - The available columns of the table selected in the Right Table drop-down list will be listed here.

In the below snap, the join field is created for SalesOrderID column in both tables.

Operator

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.

Create multiple join condition

If you want to create multiple join condition for single table relation follow the below steps:

1. Click on the 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.

Save a table relation

1. Click on the tick icon in the first row of table relation.

2. Click on the OK button.

3. Now, the second table will be dropped in the design area like below.

Edit a table relation

1. Click on the 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.

Delete a join condition

To delete a join condition in a table relation follow the below steps.

1. Open the Query Joiner dialog.

2. Mouse hover on the respective join field, to enable the delete option.

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.

Delete a table relation

1. Open the Query Joiner dialog.

2. Delete a table relation by clicking the close icon placed at right corners in first row of table relation .

3. Click on the Ok button to save the joiner state.