<< Click to Display Table of Contents >> Current:  Create Data Set > Self-Service Data Set  Association

❖Join

Drag tables or views from the database to Self-Service Data Set. When you drag a Join node to the canvas, if the distance between the join node and input nodes is within 75px, the join node will automatically connect to  input nodes. When the join node is automatically connected to the input node for the first time, the edit node dialog box will pop up automatically. If external join assosiation is defined between database tables, the default join operator based on the database external join assosiation will be added in join dialog box without user customization. Otherwise, you need to define the join relationship between the nodes manually.

Double-click or right-click the join node and choose "Edit Node" from the right menu to open the dialog for node editing. Choose operation type from the middle combo box and then define the join relationship between the two nodes. The join relationship between two nodes is as follows:

 [Input node list] The combo box marked in red line indicates all the input nodes connected to the join node. The left one is the left table, and the right one is the right table.

[Join type] Join types can be selected from the combo box marked in yellow line. Inner join, left join, right join and outer join are optional.

Inner Join: Combine the records on the left and right tables complying with the join conditions.

Left Join: Display all the records on the left table and only the records that comply with join conditions on the right table. The records not complying with join conditions on the right table are displayed as vacant.

Right Join: Display all the records on the right table and only the records that comply with join conditions on the left table. The records not complying with join conditions on the left table are displayed as vacant.

Outer Join: Display the records on both the right and left tables. For the records on the left table that do not comply with join conditions, the corresponding positions on the right table are displayed as vacant. For the records on the right table that do not comply with join conditions, the corresponding positions on the left table are displayed as vacant.

[Add] After select the left table, right table and join type, click the join column adding definition to add the definition to the table.

[Move Up/Move Down] Select the join column from the list. Adjust the definition order of the join column by clicking Move Up/Move Down which will affect the execution sequence simultaneously.

[Join execution sequence] The right pane displays the defined execution order of join relationship which will change when you click Move Up/Move Down.

Join node model is as shown below:

 

❖Union All

Join the data (including duplicate data lines) of multiple tables together through connection.The two tables for union must have the same number of columns in the table, and the data types of the corresponding columns must match. If the above two conditions are not satisfied, the association is an invalid association.