Joining Tables

NOTE

For Microsoft SQL Server Analysis Services connection type, table joining is not applicable.

Joining of tables is required when you are going to use more than one table in your data source design. The join icon (highlighted below) in the tools pane at data design view will be in disabled state, if there was only one table found dropped in table design view like below:

It will get enabled once you drop the 2nd table like below:

Adding a join condition

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.

In the above screen shot, the LeftTable illustrate the list of table dropped already. The RightTable illustrate the table which you have dropped recently and that requires to set up a relation with any of the previously dropped tables. The drop-down list below to that, represent the join condition. You can add multiple join condition for single table relation just by click Add Condition button.

The join type, compare operator and relational operator to make relationship between the two tables, can be defined through the options available in join editor.

Join Types

Two types of joins can be made between tables in join editor. They are Left Outer Join and Inner Join.

Inner Join

INNER JOIN will return the records from two or more tables, while records are matching in both the tables.

An inner join of Table1 and Table2 gives the result of Table1 intersect Table2, i.e. the inner part of a Venn diagram intersection.

For example, consider the below two tables.

Table1

Supplier_Id	Supplier_Name
100	James
101	John
102	Robert
103	Michael

Table2

Order_Id	Supplier_Id	Order_Date
20125	100	09/21/2017
20126	101	09/22/2017
20127	104	09/23/2017

If we join (INNER JOIN) Table1 and Table2 based on Supplier_Id column and equals (=) as comparison operator, then Syncfusion Dashboard will return the result like below.

Supplier_Id	Supplier_Name	Order_Id	Supplier_Id(Table2)	Order_Date
100	James	20125	100	09/21/2017
101	John	20126	101	09/22/2017

Left outer join

LEFT OUTER JOIN will return all record from the left table and the matched records from the right table. The result is NULL from the right table, if there is no match.

For example, consider the below two tables.

Table1

Supplier_Id	Supplier_Name
100	James
101	John
102	Robert
103	Michael

Table2

Order_Id	Supplier_Id	Order_Date
20125	100	09/21/2017
20126	101	09/22/2017
20127	104	09/23/2017

If we join (LEFT OUTER JOIN) Table1 and Table2 based on Supplier_Id column and equals (=) as comparison operator, then Syncfusion Dashboard will return the result like below.

Supplier_Id	Supplier_Name	Order_Id	Supplier_Id(Table2)	Order_Date
100	James	20125	100	09/21/2017
101	John	20126	101	09/22/2017
102	Robert
103	Michael

Join Condition

You can define a condition for joining two tables through any of the compare operator for comparing the values of the two columns (one from each table) by which relation between tables need to be made.

Join Condition Relationship

You can define the relationship for joining, with multiple condition, in the condition selection block.

You can also create condition using a constant value instead of choosing column as right operand to join tables.

Updating a join condition

Update an existing join condition through selecting that in the top table and then edit the mapping between columns through interacting with columns list, join type and compare operator.

If you are not at the join editor, it can be invoked through clicking the highlighted icon below in the data design view.

NOTE

Updating an existing join condition will allow you to edit the column mapping only between those two tables.

Click Update to save the changes that you made.

Click Close to close the join editor.