0

I am joining 2 tables and using <= in Join Condition also applying one filter condition so that it can only fetch remaining data from left table where filter condition is true.

I am using below query.

SELECT * FROM  test.TABLE1 T1 
left join test.TABLE2 T2 
on (
T1.low<=T2.low
) 
where  t1.ID='1';

Error:

 Error while compiling statement: FAILED: SemanticException 
[Error 10017]: Line 4:0 Both left and right aliases encountered in JOIN '0' (state=42000,code=10017)

When I am only giving '=' condition instead of <= then its running without any issue.

enter image description here

Sonu
  • 77
  • 11
  • This seems to be a valid qry. Same query works in my hive. What is your version also is it possible to share your table structure. My test SQL `select * from t2p t1 left join tmp2 on (tmp2.b <= t1.id) WHERE t1.name ='dum'` – Koushik Roy Mar 17 '22 at 14:44
  • Apache Hive (version 1.2.1000.2.6.3.0-235) @KoushikRoy For me still is showing same error. – Sonu Mar 17 '22 at 16:32
  • the old version is causing this issue i think. So, workaround is, what is your SQL supposed to do? can you do something like this - `select * from (SELECT t1.*,t2.low as t2low FROM test.TABLE1 T1 join (select MAX(low) low from test.TABLE2) T2 ) rs where t1.ID='1' and t2low <=t1.low`. notice i used a subqry and max to join. – Koushik Roy Mar 17 '22 at 17:21
  • For your example, using my query, i can see it should work. See, max of table2.low is 12. Anything less than equal to 12 should come from table 1. And then filter id=1 should further exclude unwanted ones. What is your output for my qry for your scenario in your question. – Koushik Roy Mar 18 '22 at 07:11
  • @KoushikRoy Accoring to left Join all the matching records from right table and remaining records from left table should be available so Ideally if we are using left join then 14 from left table also should be available but its not showing in output. – Sonu Mar 19 '22 at 08:27
  • Can you try, `select * from (SELECT t1.*,t2.low as t2low FROM test.TABLE1 T1 left join (select MAX(low) low from test.TABLE2) T2 ) rs where t1.ID='1' and t2low >=t1.low`. – Koushik Roy Mar 19 '22 at 16:57
  • @KoushikRoy tried It's same. since we are writing join condition in where clause, so every-time it will give result based on where filter and will not consider remaining records of left table.WHERE clause is applied after the JOIN is made. Thats by I want to write this condition in On clause. – Sonu Mar 20 '22 at 10:47

1 Answers1

0

You could do it like this:

with data as ( Select * FROM test.TABLE1 t1 WHERE t1.ID='1')
Select * 
FROM data T1
LEFT JOIN test.TABLE2 T2 on T1.low<=T2.low
Bartosz Olchowik
  • 1,129
  • 8
  • 22