CODEWITHSUNDEEP
hive
Rahul Patidar
Rahul Patidar

Reputation: 209

Multiple SubQuery expressions : Hive

I am executing one hive query in my db in which i am joining 3 table(table1,table2,table3) and then comparing table1 year column with 3 other table(table4,table5,table6) using sub queries. I'm using below query.

     select * from  table1 A INNER JOIN table2 B ON A.id =b.id 
     inner JOIN table3 c  ON A.id = c.id 
     and c.country ="India"
     where (A.year)< (select year4 from table4) 
     and (A.year1)< (select year5 from table5 ) 
     and (A.year1)< (select year5 from table5) 
     and  (A.year1)< (select year6 from table6)  limit 10;

but its giving me below error:

    Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Unsupported SubQuery Expression
    'year1': Only 1 SubQuery expression is supported.

someone please guide me how should i handle this.

EXPECTED OUTPUTenter image description here

Upvotes: 0

Views: 193

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7407

You need to put all tables in a join condition and then only you can compare year.

SELECT 
* 
FROM  
TABLE1 A 
INNER JOIN TABLE2 B ON A.ID =B.ID 
INNER JOIN TABLE3 C  ON A.ID = C.ID AND C.COUNTRY ="India"
INNER JOIN TABLE4 T4 ON T4.ID = A.ID AND  A.YEAR < YEAR4
INNER JOIN TABLE5 T5 ON T5.ID = A.ID AND  A.YEAR1 < YEAR5
INNER JOIN TABLE6 T6 ON T6.ID = A.ID AND  A.YEAR1 < YEAR6

Pls check your SQL, year5 is compared twice.

Upvotes: 1

Related Questions