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?
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.
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.
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.
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!
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.
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.
