SELECT records from a GROUP BY where string in field is 'I1' and there are no records in the GROUP BY with string 'H4'

Question

I have a data table that looks as below. I need to select records GROUPED by ref field where MessageType field is 'I1' but no records in the GROUP are MessageType 'H4'. I'm not a pro on SQL so I'm sure my code is laughable but bare with me. Also the Ref may have /001 on the end some times so I need to use SUBSTRING in the Select and GROUP BY. So in the below table I need only REF2_ABCD to be selected as it has MessageType I1 but not another record that is MessageType H4

[Table]

Ref	MessageType
REF1_ABCD	I1
REF1_ABCD/001	H4
REF2_ABCD	I1

[/table]

SELECT
SUBSTRING(Ref,1,9) AS LRN,
MessageType
FROM table1 dh
WHERE MessageType IN ('I1', 'H4')
GROUP BY SUBSTRING(Ref,1,9),MessageType
HAVING MessageType = 'I1' AND NOT MessageType = 'H4'

Answer 1

Having can be used to filter on aggregated results. In this case, I think, you do not need it.

SELECT
SUBSTRING(Ref,1,9) AS LRN,
MessageType
FROM table1 dh
WHERE MessageType IN ('I1')
  AND NOT EXISTS (SELECT 1 
                  FROM table1 t 
                  WHERE t.MessageType = 'H4' 
                   and SUBSTRING(t.Ref,1,9) = SUBSTRING(dh.Ref,1,9))
GROUP BY SUBSTRING(Ref,1,9),MessageType

Note: MessageType IN ('I1') can, if needed/wanted, be written as MessageType = 'I1'