how to create an outer join with AND and OR

Seven 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?


Accepted Solutions
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?


All 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?

Seven 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

 

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch