One Star

Tmap: precisions on Inner join rejects

Hi All
I have a doubt about the inner join in tMAP:
I am doing a inner join between 2 tables, i have activated the "catch inner join lookup reject" in the output for rejects rows.
Does the reject flow collects: 
   - ONLY all rows in A table but not in B table ?
   - OR all rows in A table but not in B table PLUS all rows in B table but not in A table ?
Thank you for the answer
Regards
6 REPLIES
Seventeen Stars

Re: Tmap: precisions on Inner join rejects

hi,
with inner join + catch inner join lookup reject, it will collect only rows that doesn't match.
By the other hand you can just do left join (not right join)
regards
laurent
One Star

Re: Tmap: precisions on Inner join rejects

Hi kzone,
thank you for your reply but i am afraid i didn't get you.
i have 2 tables A and B, how can i get A-B and B-A using INNER JOIN with reject option ?
I know that this is possible using 2 subjobs with left outer join(1st table A=main, B=lookup, 2nd vice versa) but i want to avoid doing 2 subjobs.
thank you
Seventeen Stars

Re: Tmap: precisions on Inner join rejects

unfortunatly I guess you cannot do it with just one subjob.
but if someones can prove the inverse ... Smiley Wink
regards
laurent
One Star

Re: Tmap: precisions on Inner join rejects

Thanks laurent,
so you confirm that inner join rejects takes only rows in A but not B right ?
It doesn't take rows in B but not in A?
Regards
Fifteen Stars

Re: Tmap: precisions on Inner join rejects

tMap components have the concept of a Main row and Lookup rows. The Main row is key here. If we say A table is connected as the Main row, then the tMap is driven by that table. What you are doing here is essentially a left outer join where you are catching the rows that match from your Main and Lookup as your "A=B" output, and the rows that do not match (but only from the Main) as your "A-B" output. To get B-A rows, you will need to compare the output of A=B with B and do B-(A=B). 
I believe the reason behind not having a full outer join or a right outer join is that a flow is driven by the data of the first component in that flow. If this is not strictly adhered to then data lineage can become tricky. Enabling two driving forces in the tMap components (more than 1 Main input), how would you know where the data originated?
The other way of doing this if you don't want to litter your Job with lots of tMaps is to perform this join in your source database (put the join logic into your DB input component). Ofcourse this does require that the data is from the same DB or that DB links are allowed. If you data is from the same DB I find joining and filtering before I get the data into Talend is the most efficient approach. In this situation you can do the calculation before you bring the data into Talend and use some sort of indicator column to identify A-B, B-A or A=B.
Rilhia Solutions
One Star

Re: Tmap: precisions on Inner join rejects

Thank you rhall for your detailed explanation !
Regards