Search code examples
joinhiveconditional-statementshiveql

Hive - Join based on condition

Table gr has 2 fields named ph and flag

Table ep has a field named ph

Now I need to check that for count of ph>20 in ep I should get a count of 0 for flag in the gr table if flag <> 'Y'

I have written two separate queries and need to know how to join them

select ph,count(*) from ep 
group by ph 
having count(*)>20

the results of this should be checked with gr table

Solution

  • Using inner join ON ep.ph = gr.ph and gr.flag<> 'Y':

    select gr.ph, gr.cnt 
from
(select ph, flag, count(*) cnt  
  from gr
 group by ph, flag
) gr
       inner join 
  ( select ph,count(*) from ep 
    group by ph 
    having count(*)>20 
  ) ep ON ep.ph = gr.ph and gr.flag<> 'Y'

where gr.cnt!=0 --find where count is not 0