CODEWITHSUNDEEP
sqlsubqueryimpala
tennis_fan89
tennis_fan89

Reputation: 125

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?

Upvotes: 0

Views: 1092

Answers (1)

Isolated
Isolated

Reputation: 6454

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

Upvotes: 1

Related Questions