查詢以查找失去的狀態事件

作為查詢相關表的最佳方式，我遇到了障礙。我有兩個表：main 和 status_history。

現在，我可以從 main 中辨識出根本沒有 status_history 的記錄，但是我需要從 main 中查詢沒有達到傳遞狀態的記錄。

目前查詢是：

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;

這將向我顯示所有已完成但根本沒有 status_history 的內容。問題是，典型的傳遞有多個狀態歷史記錄（為了簡單起見，我們說：新的、已確認的和已傳遞的）。

我想找到所有缺少“已傳遞”狀態歷史記錄的記錄（希望這不會產生影響，但在真正的數據庫中，我正在尋找十幾個與已傳遞等效的不同消息）。我猜這是一個簡單的子查詢，但我現在有一個心理障礙。

上面的範例模式被縮減為：

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

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

例如，main 可能如下所示：

+----+---------------------+
| 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 |
+----+---------------------+

status_history 會有這個（加上一個日期時間戳列）：

+----+---------+----------------+
| 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          |
+-------+---------+-------------+

因此，如果以上是兩個表的全部輸出，我想發現 m.id 的 2,5-10 缺少“已傳遞”狀態消息。

@spthorn 的答案是正確的，但是IN當所涉及的列可以為空時，有時會給出意想不到的結果。

如果你想避免這樣的意外，最好還是使用你已經擁有NOT EXISTS的結構。LEFT JOIN / IS NULL你需要的改變是最小的。只需s.status_message = 'Delivered'在加入ON子句中添加條件：

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 ; 

NOT EXISTS版本將是：

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

引用自：https://dba.stackexchange.com/questions/138408