Left join in tmap with an 'or' condition

EMeany — Sat, 16 Nov 2024 03:17:16 GMT

I have input flow with 3 key fields coming into tMap and would like to grab all records from lookup where ANY of the 3 keys match the lookup keys, and also pass all input rows to the output flow.

This is similar to sql: ...select * from tbl1 left join tbl2 on (tbl1.key1 = tbl2.key1 or tbl1.key2 = tbl2.key2 or tbl1.key3 = tbl2.key3)

I can do this by using 3 lookups or 3 tMaps but this seems like bad practice.

How can I do this using one tMap and one lookup?

Re: Left join in tmap with an 'or' condition

TRF — Thu, 13 Feb 2020 07:39:01 GMT

As soon as you don't have other choice than having 2 lookups or separated tMap, this not a bad practice (because tMap doesn't allow you to choose between "and" and "or").
If you really want a single tMap and a single lookup, you may produce a cartesian product (tables not joined with each other) and use a filter expression for output flow:
tbl1.key1 == tbl2.key1 || tbl1.key2 == tbl2.key2 || tbl1.key3 == tbl2.key3
It should work but in my opinion this not a good design option and in case of huge volumes it may give you poor performances and possible memory issues.

topic Re: Left join in tmap with an 'or' condition in Talend Studio

Left join in tmap with an 'or' condition

Re: Left join in tmap with an 'or' condition