Implementing inequality join in hive from mysql query

Question

I am trying to implement a query in hive which was written in mysql. I know that hive doesn't support the inequality joins on ON condition. Below is my code and tell me a way to implement it.

Select test1.a,
test2.b,
test4.c,
dummy.c
from
test1 join test2 on test1.id = test2.id and test2 != 'ABC'
join test3 on test1.id = test2.id and test3 != 'Archive'
join test4 on test3.id = test4.id and test4 = 'XYZ'
left outer join
(select test1.a,
test2,b
test3.c
from test1 join test2 on test1.id = test2.id and test2 != 'ABC'
join test3 on test1.id = test2.id) dummy
on test3.id = dummy.id
**and (test4.id != 1001 or dummy.c = TRUE)**
left join test5 on test3.id= test5.id
and dummy.c = TRUE

Now the condition highlighted with * is the part where I need to know how to implement it in hive because I cannot implement it at ON condition and if I am putting it in where clause results are not matching. Any suggestions to rewrite it in hive would be appreciated.

Answer 1

I used the inequality condition as a case statement in my SELECT statement for the columns which are being selected from the LEFT JOIN . Below is the code -

Select test1.a,
test2.b,
test4.c,
case when (test4.id != 1001 or nvl(dummy.c , False))= TRUE then dummy.c end as c0
from
test1 join test2 on test1.id = test2.id and test2 != 'ABC'
join test3 on test1.id = test2.id and test3 != 'Archive'
join test4 on test3.id = test4.id and test4 = 'XYZ'
left outer join
(select test1.a,
test2,b
test3.c
from test1 join test2 on test1.id = test2.id and test2 != 'ABC'
join test3 on test1.id = test2.id) dummy
on test3.id = dummy.id
left join test5 on test3.id= test5.id
and dummy.c = TRUE