Search code examples
sqlsubqueryimpala

Querying id's that have multiple status conditions

I have the following data in Impala:

ID STATUS Date
1 a date1
1 b date2
1 c date3
2 a date4
2 c date5
3 a date6
3 b date7
3 c date8
3 x date9

I'd like to construct a query that returns the ID's only if that ID has status (A or B or C) and status X. I also want to return the date for status X. I can't figure out a good way to do this. Would I need to join two sub queries with different status conditions?

Solution

  • This should work..

        select distinct id, date
    from t1
    where status = 'x'
    and id in 
      (select id
       from t1
       where status in ('a','b','c')
       )

    db-fiddle here: https://www.db-fiddle.com/f/uHVNeriETsXkLPV5kiSC1z/0