topic Re: how to create an outer join with AND and OR in Talend Studio

how to create an outer join with AND and OR

Anonymous — Fri, 30 Jun 2017 08:35:40 GMT

I have a MS SQL query that queries multiple views. The query is something like this

SELECT main.id, main.name, lvl1.id, lvl1.name, lvl2.id, lvl2.name, sub.sub_id
FROM main
LEFT JOIN level lvl1 ON lvl1.id = main.level1_id AND lvl1.level = 1
LEFT JOIN level lvl2 ON lvl2.id = main.level2_id AND lvl2.level = 2
LEFT JOIN sub_main sub ON sub.level_1_name = lvl1.name AND (sub.level_2_1_name = lvl2.name OR sub.level_2_2_name = lvl2.name)

I am able to code the first two LEFT JOINs in a tMap component.

I am not sure how to do the last one, especially the OR part of the JOIN.

Can anyone help me with this?

Re: how to create an outer join with AND and OR

Anonymous — Fri, 30 Jun 2017 08:40:50 GMT

You can put that whole SQL query in a tMSSQLInput component and map the schema to only the columns you are retrieving. Why would you do that in a tMap when the database is the best place to execute such join statements?

Re: how to create an outer join with AND and OR

ThWabi — Fri, 14 Jul 2017 09:44:53 GMT

Hello kchachad,

perhaps you can use this: Omit the OR condition in the query and put it as a filter condition in the output table in tMap.

Best regards,

Thomas