You can translate the question and the replies:

Condition based Join of 3 tables

I am having 3 tables - A, B, C. I need 4 kinds of output Case 1 : If condition 1 and 2 both not satisfied : Sel * from A Case 2 : If condition 1 satisfies : Sel A.* from A Inner Join B Case 3 : If condition 2 satisfies : sel A.* from A Inner Join C Case 4 : If condition 1 and 2 both satisifed : sel A.* from A inner join B inner join C (with A) Please let me know how I can achieve this in a single API.
user
09-08-2021 11:44:06 -0400

2 Answers

Lets assume Table A, B and C have two columns each respectively: A1,A2, B1,B2, C1 and C2 respectively. Each of these cases can be modelled as seperate query combined using UNIONs as follows: SELECT a1,a2, NULL AS B1, NULL AS B2,NULL AS C1,NULL AS C2 FROM A WHERE Condition1 IS NOT TRUE and Condition2 IS NOT TRUE UNION SELECT A1,A2,B1,B2,NULL AS C1,NULL AS C2 FROM A,B WHERE Condition1 IS TRUE and Condition2 IS NOT TRUE UNION SELECT A1,A2,NULL AS B1, NULL AS B2,C1,C2 FROM A,C WHERE Condition2 IS TRUE and Condition1 IS NOT TRUE UNION SELECT A1,A2,B1,B2,C1,C2 FROM A,C WHERE Condition2 IS TRUE and Condition1 IS TRUE Can you try this! Regards, Dude
user
10-08-2021 08:37:20 -0400
Hi, I was able to create this view. To accomplish this, create a selection view for the first case by navigating to New > Selection and specify the condition under the Where tab as below, `case when <condition> then true else false end` Similarly for the remaining case conditions, you can create 3 join views using the respective tables by navigating to New> Join. And specify the case condition under the where tab. Once those selection and join views are created, you can utilize the Extended Union to achieve this scenario, since it takes care of dissimilar views. To create the [Extended Union in Denodo 8.0](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/queries_select_statement/union_clause/union_clause), right-click on the Server Explorer and click File > New > Union (extended). Drag and drop the previously created views, connect them and save the view. For Denodo 7.0, please follow this [link](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/queries_select_statement/union_clause/union_clause) for implementing an Extended Union. For more details on case statements, you can refer to [CASE Clause](https://community.denodo.com/docs/html/browse/8.0/vdp/vql/queries_select_statement/case_clause/case_clause#case-clause) of the Virtual DataPort VQL Guide. Hope this helps!
Denodo Team
10-08-2021 13:41:59 -0400
You must sign in to add an answer. If you do not have an account, you can register here