how to create an outer join with AND and OR

Eight 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

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch