Highlighted
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.
1 REPLY
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)