In our previous tJoin example, we explained about the Joins. In this example, we used the Talend tMap field to perform joins.
To make this Talend tMap Joins article interesting, we are using the same data but from SQL Source. Here, we join two SQL tables using the tMap field and perform Inner Join and Left Join. The following screenshot shows the DupEmployees table data.
And the Department table data is
Talend tMap Joins Example.
In this Talend tMap Joins example, drag and drop the DBConnection and tDBCommit to establish a connection to SQL and to close the same. Next, we used tDBInput to select the DupEmployees table from the Talend repository.
Next, we used another tDBInput to select the Department table from the repository.
So, we got our two tables. Next, drag and drop the Talend tMap field.
Please connect the DupEmployee Main row with tMap. Next, connect the Department Lookup row with tMap.
Next, please click on the Map Editor button or double-click on the tMap filed.
It will open the following window. We already explained all the fields in the tMap article, so please refer to the same.
First, we have to create a connection between the two tables, i.e., row1 and row2, using the key column. So, drag the DeptID from row1 and drop it in row2 beside the DeptID.
Now, you can see the little key and row1.DepID is the expression key beside the DeptID column. It means we specified the common key columns from both tables.
Next, click on the + button will open a new pop-up window to add an output. Let me create a new output and name it as JoinsOutput.
It will add the output field. Please drag the required fields from row1 and row2 and drop them in the JoinsOutput section.
Please click on the little Talend tMap settings button to open or expand the hidden columns to check the Join Model. As you can see from the below screenshot, it has selected the Left Outer Join as the default Join Model. Let me keep this as it is and click Apply and Ok button.
In this Talend tMap Joins example, we save the output in a SQL Database. So, drag and drop the DBOutput and select the SQL Server as the server type.
Right-click on the tMap allows you to select the already created output or created a New Output (Main). Let me select the JoinsOutput that we created earlier and join it to the tDBOutput.
As you see from the below, we are creating a new Talend_tMap_Joins (if not exists) table as the destination table.
Let us run the Talend tMap Joins job.
Let me open the Management studio to check the Talend tMap Left Outer Join result.
Please open the tMap editor to add one more output for the Rejected rows and add all the columns from row1 and row2 to RejectedRows section.
Next, click on the little settings button to open or expand the hidden fields. As you can see, by default, the tMap is not catching the rejected rows, or not catching the lookup inner join rows.
Please change both the options to True to catch the rejected rows of the Inner Join.
Next, please change the Talend Join Model from Left outer Join to Inner Join by using the browse button.
Check all the Talend tMap Join properties and click Apply and Ok button.
This time, when you right-click on the tMap, you can see the new output that we created now, i.e., RejectedRows. Please select the same and join it to tLogRow and select the Table preview.
Let me run this Talend tMap inner join job. Here, you can see those four inner join rejected rows.
And the result of the Talend tMap Inner join is
