Five Stars

how to create an outer join with AND and OR

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?

Tags (3)
2 REPLIES
Employee

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

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?

Six Stars

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

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