topic Re: Joining multiple tables to single table in Talend Studio

Joining multiple tables to single table

Anonymous — Mon, 27 May 2019 15:30:39 GMT

Hi,

I'm trying to join a couple of database tables to a single table. It pretty much needs to do the following: For every row in 'klantorderregel' table with 'gereed == true' a row needs to be created in 'leveropdracht' containing information from 'klantorderregel', 'klant' and 'product'. However, when I run the job it creates 12 new rows in 'leveropdracht' while there are only 4 that need to be made. Can anyone help me? Here are some screenshots:

Re: Joining multiple tables to single table

nivedhitha — Mon, 27 May 2019 15:41:34 GMT

Hi @sjhdonge ,

What is the datatype of the column 'gereed'? If it is int, can you please try changing the filter condition on 'klantorderregel' data set(input) to row1.gereed==1 ?

Re: Joining multiple tables to single table

Anonymous — Mon, 27 May 2019 15:42:34 GMT

It's boolean. If I remove the expression it will output 24 rows, so I suppose the expression is correct. I think it has something to do with no expr. key existing between klantorderregel and klant. It must actually somehow see what 'klant' (translated customer) is connected to a 'klantorderregel' without the existing column in klantorderregel.

Re: Joining multiple tables to single table

nivedhitha — Mon, 27 May 2019 15:47:45 GMT

@sjhdonge ,

okay, so then your filter expression is right.

I see that you are making an inner join, but there are no fields from dataset1 that is pulled to dataset2. marked in red below. So the join is not actually happening.

Re: Joining multiple tables to single table

Anonymous — Mon, 27 May 2019 15:51:14 GMT

Yeah I see. Is the only way to solve this by making a new column in dataset 1 and link it to a column from dataset 2?

Re: Joining multiple tables to single table

nivedhitha — Mon, 27 May 2019 15:58:33 GMT

@sjhdonge ,

no you don't have to make a new column. columns from the 1st dataset that you are using to join it with dataaset2 should be dragged to the corresponding column in dataset2.

in the above screenshot im trying to join 2 datasets on Project_Id column and so I have Project_Id from row1 against Project_Id in row2 dataset and im making a left outer join here.

please take a look at this link if it is not clear
https://www.talend.com/resources/joining-two-data-sources-with-tmap-component/

Re: Joining multiple tables to single table

Anonymous — Tue, 28 May 2019 16:37:25 GMT

I'll take a look at it tonight