Six Stars

multiple joins in single tJoin

I want to apply 2 joins in a tMap component. first one is inner join and second one is left outer join. However i am unable to do so. Someone suggested me to use tJoin reject for left outer join. However, logically speaking rejected of inner join is everything that isn't inner join, which is combination of left outer join and right outer join. Can anyone help?

  • Data Integration
11 REPLIES
Ten Stars

Re: multiple joins in single tJoin

Could you do a left join for both fields and then filter out null values resulting from the join you wanted to be inner?

 

Edit: To elaborate, tMap doesn't support inner and outer joins to the same lookup source, and it doesn't sound like tJoin will do what you're looking for either.

Six Stars

Re: multiple joins in single tJoin

Is there any workaround or alternative solution for this problem?

Ten Stars

Re: multiple joins in single tJoin

You could do a left join on both fields and include the key value from the lookup feed in the output, then filter records where the inner join lookup key is null.
Nine Stars

Re: multiple joins in single tJoin

other way is use staging database (even local SQLite) - and use full power of SQL

 

time is money and sometime addition step make screen much more cleaner, code much more readable, and final result for less time.

 

 

 

-----------
Six Stars

Re: multiple joins in single tJoin

Not entirely sure what you meant. Can you elaborate a little further?

Ten Stars

Re: multiple joins in single tJoin

Load the data into a database and then extract it with a sql statement that performs the join you want. If you don't have a persistent database available and can't set one up locally, the tHSQLDb components have a "HSQLDb In Memory" option which will create a temporary database that is torn down at the end of the job.

You would attach your input and lookup flows to separate tHSQLDbOutput components, creating two tables, then use tHSQLDbInput to join them and retrieve the values you need.
Six Stars

Re: multiple joins in single tJoin

Even more confused. Sorry, can you suggest what you meant by "You could do a left join on both fields and include the key value from the lookup feed in the output, then filter records where the inner join lookup key is null."? New answer is somewhat different from original answer. I got a feeling that your original comment has potential. Thanks!

Nine Stars

Re: multiple joins in single tJoin


Enthusiast wrote:

Not entirely sure what you meant. Can you elaborate a little further?


Talend support JOINs, but functionality is limited.

Often - fasted and more flexible way - make this in database

 

SQL support JOINS, lookups and other conditions in single complicated query. So if You experienced in databases - this is allow You achieve expected result faster and code could be more readable.

 

Also - Talend very effective work with in memory operations, but when data bigger than memory - it time to problems. Opposite - databases oriented for work with data much more bigger than RAM. 

 

 

-----------
Six Stars

Re: multiple joins in single tJoin

I am required to do this job in a windows server without any database. I am reading all the data form multiple CSV and JSON files. Asking for a database to the company management is not an option for me. I will need to find any workaround within Talend to be able to do it. Thanks though.

Nine Stars

Re: multiple joins in single tJoin

up to You of course,

 

but for use SQLite for example - do not need request any permissions from management :-) (very often used way with Python ETL scripts)

-----------
Ten Stars

Re: multiple joins in single tJoin


Enthusiast wrote:

Even more confused. Sorry, can you suggest what you meant by "You could do a left join on both fields and include the key value from the lookup feed in the output, then filter records where the inner join lookup key is null."? New answer is somewhat different from original answer. I got a feeling that your original comment has potential. Thanks!


Use a tMap to join your main flow to your lookup flow.  Join on both key columns, and set the join type to Left Outer Join.  Add a field to your output schema containing the key from the lookup flow that you wanted to be an inner join.  Any records generated where the lookup key is null you want to filter out.  You can filter those records a number of ways.  A tFilterRow component is an obvious choice, but you can also add an expression filter to your tMap output.

 

If this is still confusing, you should probably attach some screenshots of your job and your tMap configuration so we can give more specific advice.