Query to find missing status events

Question

I'm hitting a road block as the best way to query a related table. I have two tables: main and status_history.

Right now, I can identify records from main with no status_history at all, but I need to query records from main that did not make it to the delivery status.

Current query is:

SELECT
   m.id
   ,m.date_completed
   ,s.status_message
FROM
   main m
   LEFT JOIN status_history s ON s.main_id on m.id
WHERE
   m.date_completed IS NOT NULL and s.status_message is null;

That will show me everything that has been completed but has no status_history at all. The problem is, that a typical delivery has multiple status histories (let's say for simplicity: new,confirmed and delivered).

I want to find all records that are missing the 'delivered' status history (hopefully this won't make a difference, but in the real DB, I'm looking for a dozen different messages that are equivalent of delivered). I'm guessing this is a simple sub-query but I'm having a mental block right now.

Examle schema for the above is pared down to:

main table:
id (Unique Key)
date_completed (datetime)

status_history table:
id (unique key)
main_id (INT)
status_message (varchar)

For example, main may look like:

+----+---------------------+
| id | complete_date       |
+----+---------------------+
|  1 | 2016-05-09 09:12:09 |
|  2 | 2015-05-21 14:39:16 |
|  3 | NULL                |
|  4 | 2015-05-21 13:57:05 |
|  5 | 2015-05-21 14:43:17 |
|  6 | 2015-05-26 08:49:17 |
|  7 | 2016-05-09 14:20:06 |
|  8 | 2015-05-26 14:07:53 |
|  9 | 2015-05-21 15:16:08 |
| 10 | 2015-05-21 15:34:02 |
+----+---------------------+

And status_history would have this (plus a datetime stamp column):

+----+---------+----------------+
| id | main_id | status_message |
+----+---------+----------------+
| 1  |   1 | New                |
| 2  |   1 | Confirmed          |
| 3  |   1 | Delivered          |
| 4  |   3 | New                |
| 5  |   4 | New                |
| 6  |   3 | Confirmed          |
| 7  |   4 | Confirmed          |
| 8  |   5 | New                |
| 9  |   5 | Confirmed          |
| 10 |   4 | Delivered          |
+-------+---------+-------------+

So, if the above was the entire output of both tables, I want to find that m.id's 2,5-10 are missing "Delivered" status messages.

Answer 1

@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'
        ) ;

Answer 2

Something like this:

SELECT id FROM main
WHERE id NOT IN (
    SELECT main_id FROM status_history WHERE status_message IN ('Delivered')
)

will do the trick.

Answer 3

Thanks all, I suppose there's multiple ways to skin the cat on this one. @spthorn's didn't work on the production data set since it was too big. I found that similar to ypercube's suggestion, the LEFT JOIN works much more efficiently. My final query was slightly different than suggested but will add it here as a third (or fourth) option:

SELECT
    m.id,
    m.date_completed
FROM
    main m
    LEFT JOIN (select s.main_id from status_history s where s.status_message = 'Delivered')) AS a
    ON m.id = a.main_id
WHERE
    a.status_message IS NULL AND m.date_completed IS NOT NULL;