3

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.

  • 1
    Before we begin I think your posted query is incorrect. To show main records with no status history at all you would have WHERE s.main_id is null. Can you please recheck and possibly include schema for your tables so that others can attempt an answer? – Cody Konior May 13 '16 at 4:56
  • Added more detail – RyanH May 13 '16 at 8:46
2

@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'
        ) ;
  • Since you want to know whether a thing does or does not exist (the Delivered status), the EXISTS form should be preferred. Also it may give the optimiser better options to cease looking earlier than the JOIN form. YMMV. – Michael Green May 14 '16 at 10:42
  • There was a typo in 2nd query, it has been corrected now. – ypercubeᵀᴹ May 14 '16 at 19:42
1

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.

0

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;
  • Your query is equivalent, yes. It might force a subquery materialization in some MySQL versions and thus it might be more efficient, if there are indexes missing.. With an index on (status_message, main_id), my queries should give more options/paths to the optimizer and should be preferred. – ypercubeᵀᴹ May 14 '16 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.