CODEWITHSUNDEEP
hivehiveql
Matt W.
Matt W.

Reputation: 3722

subquerying in hive with left outer join or where exists

I'm trying to do multiple nested subqueries. First find lowercase names with role "Person", then find session_ids that those people have had. With that list of session ids, I want to filter the original query, by all results that are in those session ids

select 
U.session_id,
U.session_date,
U.email
from data.usage U
left outer join
  select 
  distinct M.session_id
  from data.usage M
  where email like '%gmail.com%'
  and data_date >= '20180101'
  and name in
    ( 
    select 
    lower(name)
    from data.users
    where role like 'Person%' 
    and isactive = TRUE
    and data_date = '20180412'
    )
on U.session_id = M.session_id

I'm able to get the subqueries to work on their own, and results in a list of session_ids, I've tried using where session_id in (subqueries) but that doesn't work. I've also tried doing something like where exists, or left outer join, but I haven't be able to get any of them to work.

Upvotes: 0

Views: 664

Answers (1)

notNull
notNull

Reputation: 31540

After left outer join keep alias name for the all the query you have written to M then run the query again.

Try to run below Query:

select 
U.session_id,
U.session_date,
U.email
from data.usage U
left outer join
  (select 
  distinct M.session_id
  from data.usage M
  where email like '%gmail.com%'
  and data_date >= '20180101'
  and name in
    ( 
    select 
    lower(name)
    from data.users
    where role like 'Person%' 
    and isactive = TRUE
    and data_date = '20180412'
    ))M
on U.session_id = M.session_id

Upvotes: 1

Related Questions