mysql - 在sql查询中使用exists的两种情况

Question

我正在尝试进行查询，内容如下:如果客户没有发票，但在过去 6 个月内有预约，请给我他们的 clientId 和姓名 以下结果返回并为空集.

SELECT clients.clientId, clients.studentFirstName, clients.studentLastName 
FROM  clients, invoices, appointments 
WHERE (NOT EXISTS 
          (SELECT * 
           FROM invoices, clients 
           WHERE invoices.clientId = clients.clientId)) 
AND (EXISTS 
         (SELECT * FROM appointments, clients 
         WHERE appointments.clientId = invoices.clientId 
         AND appointments.date >= DATE_ADD(curdate(), INTERVAL 6 MONTH)));

编辑:最终工作的查询是在对约翰的答案进行一些调整后创建的:

 SELECT  a.clientID,
    a.studentFirstName,
    a.studentLastName
FROM    clients a 
        LEFT JOIN invoices b
            on a.clientID = b.clientID
        LEFT JOIN appointments c
            on a.clientID = c.clientID
WHERE   b.clientId IS NULL AND
    c.`date` >= DATE_SUB(curdate(), INTERVAL 6 MONTH)

Answer 1

改用LEFT JOIN。

SELECT  a.ClientID,
        a.studentFirstName,
        a.clients.studentLastName
FROM    clients a 
            LEFT JOIN invoices b
                on a.ClientID = b.ClientID
            LEFT JOIN appointments c
                on a.ClientID = c.ClientID
WHERE   b.Client IS NULL AND
        c.`Date` >= DATE_SUB(curdate(), INTERVAL 6 MONTH)