mysql - 多个“否”但一个"is"SQL

Question

我正在尝试获取一个查询，其中用户有多次续订，但其中只有一个设置为"is"。我想得到一个查询，其中如果所有这些都不是(0)则返回他们的名字。

SELECT u.id, CONCAT(u.fname, " ", u.lname) as full_name, renewals.renewRemind
     FROM user u 
INNER JOIN userRenewals renewals ON u.id = renewals.user_id
WHERE u.status = 1
AND renewals.renewRemind = 0
HAVING COUNT(renewals.renewRemind == 1) < 1

示例

Billy - Renewal
ID: 39 - YES
ID: 40 - NO
ID: 43 - NO
ID: 50 - NO

 Vicky - Renewal
    ID: 78 - NO
    ID: 80 - NO
    ID: 100 - NO
    ID: 119 - NO

Vicky 应该出现在查询中，而 billy 则不会。

Answer 1

我会使用NOT EXISTS:

SELECT u.id, CONCAT(u.fname, " ", u.lname) AS full_name, r.renewRemind
FROM user u INNER JOIN 
     userRenewals r 
     ON u.id = r.user_id
WHERE NOT EXISTS (SELECT 1 FROM userRenewals r1 WHERE r1.user_id = r.user_id AND r1.renewRemind = 1);