Join Views

Joining of Views is required when the dataset query design demands for more than one View. The following sections describes the steps required to create and edit the relationships between Views. Refer Create Data section for better understanding with the following steps.

Open query joiner dialog

1. When you drag and drop two Views from the list
   or

2. When click on the Join icon in the query designer toolbar.

Create a View relation

1. If the subsequent View being dropped, has any of its column as foreign key in any of the already dropped Views, 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 View and any one of the already dropped Views.

View drop-down list

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

• Right View - The Right View drop-down list illustrates the list of View dropped in design area. By default, the View which you have dropped recently will be selected in this field.

In the below snap, the new relationship is initiated between REP_INVOIVEView and REP_INVOICEDETAILView Views.

Join Types

The joins are used to retrieve data from two or more data Views, based on a join condition. These are supported join types

1. Inner Join

2. Left Outer

3. Right Outer

4. Full Outer.

In the below snap, inner join is created between the REP_INVOIVEView and REP_INVOICEDETAILView Views.

View fields

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

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

In the below snap, the join field is created for InvoiceID column in both Views.

Operator

To compare the values of the two columns (one from each View) between Views, any of the operator list shown in the below image can be used.

In the below snap, the Equal operator is applied between InvoiceID column of both Views.

Create multiple join condition

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

1. Click on the Add Field button to create multiple join conditions for single View 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 View relation

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

2. Click on the OK button.

Edit a View 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 View 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 View 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.

Supported Join Types

The following example describes the supported join types list and purpose of the each join type.

Inner join

INNER JOIN will return the records from two or more Views, while records are matching in both the Views. An inner join of View1 and View2 gives the result of View1 intersect View2. For example, consider the below two Views.

View1

View2

If we join (INNER JOIN) View1 and View2 based on Supplier_Id column and equals (=) as comparison operator, then the result will be like below.

Left outer join

LEFT OUTER JOIN will return all record from the left View and the matched records from the right View. The result is NULL from the right View, if there is no match. For example, consider the below two Views.

View1

View2

If we join (LEFT OUTER JOIN) View1 and View2 based on Supplier_Id column and equals (=) as comparison operator, then the result will be like below.

Right outer join

Right outer join preserves the unmatched rows from the second (right) View, joining them with a NULL in the shape of the first (left) View. For example, consider the below two Views.
View1

View2

If we join (RIGHT OUTER) View1 and View2 based on EmployeeID column and equals (=) as comparison operator, then the result will be like below.

Full outer join

The FULL OUTER JOIN keyword return all records when there is a match in either left (View1) or right (View2) View records. For example, consider the below two Views.

View1

View2

The FULL OUTER JOIN keyword returns all the rows from the left View (View1), and all the rows from the right View (View2).