CODEWITHSUNDEEP
hivehiveql
JKC
JKC

Reputation: 2618

Hive - Multiple sub-queries in where clause is failing

I am trying to create a table by checking two sub-query expressions within the where clause but my query fails with the below error :

Unsupported sub query expression. Only 1 sub query expression is supported

Code snippet is as follows (Not the exact code. Just for better understanding) :

Create table winners row format delimited fields terminated by '|' as
select
  games,
  players
from olympics
where
exists (select 1 from dom_sports where dom_sports.players = olympics.players)
and not exists (select 1 from dom_sports where dom_sports.games = olympics.games)

If I execute same command with only one sub-query in where clause it is getting executed successfully. Having said that is there any alternative to achieve the same in a different way ?

Upvotes: 0

Views: 102

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7407

Of course. You can use left join. Inner join will act as exists. and left join + where clause will mimic the not exists. There can be issue with granularity but that depends on your data.

select distinct 
  olympics.games,
  olympics.players
from olympics
inner join  dom_sports dom_sports  on dom_sports.players = olympics.players 
left join dom_sports dom_sports2 where dom_sports2.games = olympics.games
where dom_sports2.games is null

Upvotes: 1

Related Questions