Query to find missing status events
@spthorn's answer is correct but IN gives sometimes unexpected results when the columns involved are nullable.

If you want to avoid such surprises, it's better to use NOT EXISTS or the LEFT JOIN / IS NULL structure you already have. The change you need is minimal. Just add the s.status_message = 'Delivered' condition in the joining ON clause:

SELECT
    m.id,
    m.date_completed
    -- ,s.status_message      -- no need for this either
FROM
   main m
   LEFT JOIN status_history s ON  s.main_id on m.id
                              AND s.status_message = 'Delivered'
WHERE
    m.date_completed IS NOT NULL 
    AND s.main_id IS NULL ; 


The NOT EXISTS version would be:

SELECT
    m.id,
    m.date_completed
FROM
   main m
WHERE
    m.date_completed IS NOT NULL 
    AND NOT EXISTS
        ( SELECT *
          FROM status_history s
          WHERE s.main_id = m.id
            AND s.status_message = 'Delivered'
        ) ;