mysql - 排除两行，其中一行不匹配条件 - 自连接？

Question

我有一个表，用于存储一个人在任何给定日期的茶偏好。 ;)

customer_id, tea_preference,     date
     1        'yes please'    2011-01-01
     1        'no thanks'     2011-01-02

我需要找到在给定日期范围内喜欢茶的客户，例如:

SELECT * FROM customers
WHERE tea_preference = 'yes please'
AND date BETWEEN '2011-01-01' AND '2011-01-03'

现在，该查询将给出一个匹配项，但如果客户不喜欢该范围内的某个日期的茶，我不希望该客户有任何匹配项...

我尝试过自连接，但要么它们不是答案，要么我做得不对，因为我似乎无法获得我想要的结果集。帮忙？!

Answer 1

也许:

SELECT c1.* FROM customers c1
WHERE c1.tea_preference = 'yes please' 
AND c1.date BETWEEN '2011-01-01' AND '2011-01-03'
AND NOT EXISTS 
    (SELECT c2.* FROM customers c2 
    WHERE c2.tea_preference = 'no thanks'
    AND c2.date BETWEEN '2011-01-01' AND '2011-01-03' 
    AND c1.custom_id = c2.customer_id)