double join between two tables in one tMap?

One Star

double join between two tables in one tMap?

Hello,
Is it possible to do a double join between two tables in one tMap?
Example:
I have two tables:
Table bill {pki, prescriberID, executorID)
Table doctors {pki, firstname, etc...)
I need to do a join between "bill.prescriberID" and "doctors.pki", and a join between "bill.executorID" and "doctors.pki".
I could do 2 tMap one after the other but that would require me to load 2 times the doctors table, which degrades performance ...
Is it possible to make the two joints in one tMap?
Thank you in advance for your help.
One Star

Re: double join between two tables in one tMap?

Hi,
Do the join in the SQL for the input component and map the unified result set in the tMap. Be sure to use LEFT OUTER JOIN. Something like
SELECT b.pki, prescriberID, executorID, d.firstname prescriberFirstname, d_2.firstname executorFirstname
FROM bill b
LEFT OUTER JOIN doctors d ON (d.pki=b.prescriberID)
LEFT OUTER JOIN doctors d_2 ON (d_2.pki=b.executorID)

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download