topic Re: Inner join multiple tables in Talend Studio

Inner join multiple tables

Anonymous — Wed, 01 Apr 2015 15:57:11 GMT

Hi
I am facing a challenge while joining (Inner) more than 2 tables.
I have 3 tables as below:
1. Employee Details
________________________________
|ID |Name |Mobile |
________________________________
|101 |John |1234567890 |
________________________________

2. Dependant Details:
________________________________
|ID |Dependant Name |Age |
________________________________
|101 |Chris   |21 |
|101 |Mark   |24 |
________________________________

Email address Details:
________________________________________
|ID |Email Type |Email   |
________________________________________
|101 |Primary   |aaa@Talend.com |
|101 |Secondary   |bbb@Talend.com |
|101 |Other |ccc@Talend.com |
________________________________________

I need result/output table as below after joining all the above 3 tables:
________________________________________________________________________________________
|ID |Name |Mobile |Dependant Name |Age |Email Type |Email   |
________________________________________________________________________________________
|101 |John   |1234567890 |Chris |21 |Primary   |aaa@Talend.com |
|101 |John   |1234567890 |Mark   |24 |Secondary   |bbb@Talend.com |
|101 |John   |1234567890 |Mark   |24 |Other |ccc@Talend.com |
________________________________________________________________________________________

I tried to join 3 tables using tMap component. First table as mail table and other 2 tables as lookup tables. And I used Inner Join for this. But I am getting 6 records instead of 3 records. record count should be the maximum record count of all 3 tables i.e., 3 (record count in Email). We are not sure in which table we get maximum number of records.
Please help me to achieve the above.
Thanks,
Sravan

Re: Inner join multiple tables

Anonymous — Wed, 01 Apr 2015 16:25:30 GMT

hi,
I guess that you're using "id" to join table, but as "dependent details" as 2 rows 're matching "101", and you've got 3 input rows looking for match id = 101 => 6 row as result.
Which is the rule that tell us how to get "mark" with Secondary, Chris with Primary & Mark (again) with Other.
Miss something in your workflow
regards
laurent

Re: Inner join multiple tables

Anonymous — Thu, 02 Apr 2015 10:20:01 GMT

Hi Kzone,
I understand that we get 6 rows as a result. But I am trying optimize the result wherein we get the result as 3 rows which I showed in my above my post. Do we have any components or any other way to achieve after the completion of join.
Thanks,
Sravan

Re: Inner join multiple tables

Anonymous — Thu, 02 Apr 2015 13:40:42 GMT

You can use the tAggregate components to remove duplicates for you.
I am curious, are all of the tables in the same database? If so, why not join in SQL in one of the t{DB}Input components? You can distinct the query (if you can't find more elegant SQL logic to remove the duplicates) and there is little point filtering data like this in Talend when your DB will be quicker.
A rule of thumb that I use is that if the data needs to be filtered and is coming from one database, filter as much as you can before it gets to Talend. Java is relatively slow at data manipulation and filtering by comparison to a DBMS, so you may as well use the tools available to you rather than shoe-horning everything through Talend.