CODEWITHSUNDEEP
sql
Kenshin H
Kenshin H

Reputation: 71

SQL: Select rows that contains at least two items and one other item

I need to verify that a list of information given to me contains the information that has at least two items - engine (starts with either 'E' or 'PE') - and one another item - chief unit (starts with 'BC').

My initial query was this and I need help modifying it to show me the correct data. 

select distinct IncidentNum, Unit
from unit
where (unit like 'E%' 
 or unit like 'PE%'
 or unit like 'BC%')
 and unit not like 'EMS%'
and IncidentNum = '19-00001912'
group by incidentnum, unit
having count(*) > 2

Upvotes: 0

Views: 1066

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use conditional aggregation to find the incidentnums that match the conditions:

select IncidentNum
from unit
group by IncidentNum
having sum(case when unit like 'E%' or unit like 'PE%' then 1 else 0 end) >= 2 and  -- meets P/E condition
       sum(case when unit like 'BC%' then 1 else 0 end) > 0 and                    -- meets BC condition

You can modify the having clause to get the inverse -- the ones that don't match both conditions (= 0 or = 0).

I do not know what these conditions are for:

  • unit not like 'EMS%'
  • IncidentNum = '19-00001912'

Because they are not part of your question.

Upvotes: 1

Related Questions