0

I've tried to accomplish this in a few different ways, and I'm coming up short. I'm trying to figure out the IDs of clients who have not scheduled an appointment this year.

My first query takes forever, and times out most of the time:

SELECT c.clientId
FROM clients c
WHERE NOT EXISTS (
   SELECT a.appointmentId FROM appointments a WHERE a.date >= "2020-02-15" AND c.clientId = a.clientId)

My second query is super fast, but that's likely because it's not taking the NOT IN into account. The results show the same thing as if I run the query without the NOT EXISTS. I think this means I'm not properly matching up a.clientId = c.clientId. But, I can't seem to figure out how to make that work.

SELECT c.clientId
FROM clients c
WHERE c.clientId NOT IN (
    SELECT a.appointmentId FROM appointments a WHERE a.date >= "2020-02-15")

I also tried it this third way with no luck, it returns an empty set:

SELECT c.clientId
FROM clients c
LEFT JOIN appointments a
ON a.clientId = c.clientId
WHERE a.appointmentId IS NULL
AND a.date >= '2020-02-15'

Thanks in advance!

radleybobins
  • 863
  • 5
  • 10
  • 23

1 Answers1

1

The problem with your second query is that you're comparing clientId in client with appointmentId in appointments, so it's unlikely to do any filtering. It should be written as

SELECT clientId
FROM clients
WHERE clientId NOT IN (
    SELECT clientId FROM appointments WHERE date >= "2020-02-15")

The issue with your third query is that the a.date >= '2020-02-15' condition in the WHERE clause effectively turns your LEFT JOIN into an INNER JOIN (see the manual), thus a.appointmentId IS NULL is never true. You need to add the date comparison to the JOIN condition:

SELECT c.clientId
FROM clients c
LEFT JOIN appointments a
ON a.clientId = c.clientId AND a.date >= '2020-02-15'
WHERE a.appointmentId IS NULL

Note that if all you want is the clientId value, you don't need to use the clients table at all:

SELECT clientId
FROM appointments
GROUP BY clientId
HAVING MAX(date) < '2020-02-15'

Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95
  • Great catch on the second query. I can't believe I missed that after playing with it all afternoon. But even with the modification, it still returns the same number as without the NOT IN clause. Running the third query now and it's taking a while, but the database is pretty big. I'll mark this if it works! – radleybobins Apr 24 '20 at 00:52
  • Yes, your modification to the third query worked. I really appreciate your help. I can't mark an answer for another 3 minutes, but wanted to let you know. The second query does the same thing even if I have WHERE 1 NOT IN..., so I'm not sure why it's deciding to ignore that altogether. – radleybobins Apr 24 '20 at 00:56
  • 1
    @radleybobins I've added a demo which shows the second query working. Also I've added another alternate query which should be even faster if *all* you want is the `clientId` value. – Nick Apr 24 '20 at 01:03
  • I know you're supposed to keep it strictly business in the comments, but I just wanted to thank you again and apologize for not responding to your updated Demo. I came down with a case of food poisoning and I'm just getting back to normal. Your answer and comments were super helpful, and I can't thank you enough for getting me over the hump. – radleybobins Apr 25 '20 at 16:02
  • @radleybobins no problem at all - sorry to hear about the food poisoning, I know just how bad that can be. Anyway, I'm glad it was helpful. – Nick Apr 26 '20 at 00:37