CODEWITHSUNDEEP
mysqlsql
radleybobins
radleybobins

Reputation: 981

using two cases of exists in sql query

I am trying to make a query that says If the customer has no invoice, but has an appointment in the last 6 months, please give me their clientId and name The following result returns and empty set.

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

EDIT: The query that ended up working was created after a little tweaking of john's answer:

 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)

Upvotes: 0

Views: 108

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

Are you sure it's supposed to be DATE_ADD and not DATE_SUB ?

You can use joins:

SELECT    a.clientId,
          a.studentFirstName,
          a.studentLastName
FROM      clients a
JOIN      appointments b ON a.clientId = b.clientId 
          AND b.date >= CURDATE() - INTERVAL 6 MONTH
LEFT JOIN invoices c ON a.clientId = c.clientId
WHERE     c.clientId IS NULL

Upvotes: 0

John Woo
John Woo

Reputation: 263683

Use LEFT JOIN instead.

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)

Upvotes: 3

Related Questions