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

Answer

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.