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.

Employee Table

The Department table data is

Department Table

Talend tMap Joins Example.

In this Talend tMap Joins example, drag and drop the DBConnection and tDBCommit to establish a connection to SQL and close the same. Next, we used tDBInput to select the DupEmployees table from the Talend repository.

tDBInput in the employee table

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

Second database Input

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.

Editor in Component Tab of Talend tMap to perform Joins

Next, please click on the Map Editor button or double-click on the tMap field. 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.

Mark the Key Column

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, clicking 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.

Add the Output Name

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

Talend tMap to perform 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 the Apply and OK button.

Check the Model of the Talend tMap to perform Joins

In this Talend tMap Joins example, we save the output in an 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 create a New Output (Main). Let me select the JoinsOutput that we created earlier and join it to the tDBOutput.

Set the Mapping Output

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

Configure the output table name

Let us run the Talend tMap Joins job.

Run the Talend tMap Job to perform Joins

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

Result Table

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.

Caching the rejected rows in Talend tMap to perform Joins

Please change both 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.

Talend tMap to perform Inner Join

Check all the Talend tMap Join properties and click the Apply and OK buttons.

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 join it to tLogRow and select the Table preview.

Set the Rejected Rows

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

Run the Talend tMap Job to perform Inner Join

The result of the Talend tMap Inner join is

Destinnation Table