mysql - 如何查找一个表中确实存在于另一个表中但基于日期的记录？

Question

我想找到所有2015年还没有订单的经销商。我知道这个查询不起作用，但我认为这可能有助于理解我想要做什么。在此示例中，我只想得到“Bob”作为结果。他是本例中唯一一位尚未收到 2015 年订单的经销商。

SELECT d.`name` 
FROM z_dealer d 
LEFT JOIN z_order o ON (d.promo_code = o.promo_code) 
WHERE o.promo_code IS NULL
AND o.date_ordered > '2015-01-01 00:00:00'

这是表格数据...

mysql> Select * from z_order;
+----+-------+------------+---------------------+
| id | total | promo_code | date_ordered        |
+----+-------+------------+---------------------+
| 1  | 10    | holiday    | 2014-06-22 09:06:50 |
| 2  | 20    | special    | 2015-06-22 09:07:04 |
| 3  | 15    | holiday    | 2015-03-01 09:07:23 |
| 4  | 45    | special    | 2014-09-03 09:07:33 |
| 5  | 16    | laborday   | 2014-06-22 09:09:01 |
+----+-------+------------+---------------------+



mysql> select * from z_dealer;
+----+------+------------+
| id | name | promo_code |
+----+------+------------+
| 1  | John | holiday    |
| 2  | Suzy | special    |
| 3  | Bob  | laborday   |
+----+------+------------+

Answer 1

select d.`name`
from z_dealer d
where
  (select count(*)
   from z_order o
   WHERE o.promo_code = d.promo_code
   AND o.date_ordered > '2015-01-01') = 0