Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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?

Labels (2)
1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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?

View solution in original post

2 Replies
Anonymous
Not applicable
Author

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?

ThWabi
Creator II
Creator II

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