Talend tMap Joins

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.

Talend tmap Joins 1

And the Department table data is

Talend tmap Joins 2

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.

Talend tmap Joins 3

Next, we used another tDBInput to select the Department table from the repository.

Talend tmap Joins 4

So, we got our two tables. Next, drag and drop the Talend tMap field.

Talend tmap Joins 5

Please connect the DupEmployee Main row with tMap. Next, connect the Department Lookup row with tMap.

Talend tmap Joins 6

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.

Talend tmap Joins 7

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 the row2 beside the DeptID.

Talend tmap Joins 8

Now, you can see the little key and row1.DepID as the expression key beside the DeptID column. It means we specified the common key columns from both the tables.

Talend tmap Joins 9

Next, click on the + button will open a new pop-up window to add an output. Let me create a new output and named it as JoinsOutput.

Talend tmap Joins 10

It will add the output field. Please drag the required fields from row1 and row2 and drop it in the JoinsOutput section.

Talend tmap Joins 11

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.

Talend tmap Joins 12

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.

Talend tmap Joins 13

As you see from the below, we are creating a new Talend_tMap_Joins (if not exists) table as the destination table.

Talend tmap Joins 14

Let us run the Talend tMap Joins job.

Talend tmap Joins 15

Let me open the Management studio to check the Talend tMap Left Outer Join result.

Talend tmap Joins 16

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.

Talend tmap Joins 17

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.

Talend tmap Joins 18

Please change both the options to True to catch the rejected rows of the Inner Join.

Talend tmap Joins 19

Next, please change the Talend Join Model from Left outer Join to Inner Join by using the browse button.

Talend tmap Joins 20

Check all the Talend tMap Join properties and click Apply and Ok button.

Talend tmap Joins 21

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.

Talend tmap Joins 22

Let me run this Talend tMap inner join job. Here, you can see those four inner join rejected rows.

Talend tmap Joins 23

And the result of the Talend tMap Inner join is

Talend tmap Joins 24