Reducing four MySQL queries to one

Question

I'm running four statements to see how many appointments are passed due, due today, due this week or any records that don't have appointments (unused).

I have two tables, one for records (contacts) and another for the appointments (appointments) in a single to many relationship. Each contact is unique in the [contacts] table but can have multiple records in the [appointments] table (to maintain history) and the highest appointments.id is the most recent appointment for the contact.

So, currently I have:

SELECT COUNT(*) AS "Late" FROM contacts c JOIN (SELECT MAX(id) max_id, contact_id FROM appointments GROUP BY contact_id) c_max ON (c_max.contact_id = c.id) JOIN appointments a on (a.id = c_max.max_id) WHERE c.active = 1 AND a.appointment < CURDATE()

SELECT COUNT(*) AS "Today" FROM contacts c JOIN (SELECT MAX(id) max_id, contact_id FROM appointments GROUP BY contact_id) c_max ON (c_max.contact_id = c.id) JOIN appointments a on (a.id = c_max.max_id) WHERE c.active = 1 AND DATE_FORMAT(a.appointment, "%Y-%m-%d") = DATE_FORMAT(NOW(), "%Y-%m-%d")

SELECT COUNT(*) AS "This Week" FROM contacts c JOIN (SELECT MAX(id) max_id, contact_id FROM appointments GROUP BY contact_id) c_max ON (c_max.contact_id = c.id) JOIN appointments a on (a.id = c_max.max_id) WHERE c.active = 1 AND a.appointment > CURDATE() + INTERVAL 1 DAY AND a.appointment < CURDATE() + INTERVAL 7 DAY

SELECT COUNT(*) AS "Unused" FROM contacts WHERE active = 1 AND id NOT IN (SELECT contact_id FROM appointments)

And these work fine, but I thought I might try and squeeze them in to one query to run faster (?) and this is where I come unstuck, I tried many variations of:

SELECT 
    CASE 
        WHEN a.appointment < CURDATE() THEN "Late" 
        WHEN DATE_FORMAT(a.appointment, "%Y-%m-%d") = DATE_FORMAT(NOW(), "%Y-%m-%d") THEN "Today" 
        WHEN a.appointment > CURDATE() + INTERVAL 1 DAY AND a.appointment < CURDATE() + INTERVAL 7 DAY THEN "This Week" 
        WHEN c.id NOT IN (SELECT contact_id FROM appointments) THEN "Unused" 
        ELSE "Error"
    END AS "Status", 
    COUNT(a.appointment) AS "Count"
FROM contacts c JOIN (SELECT MAX(id) max_id, contact_id FROM appointments GROUP BY contact_id) c_max ON (c_max.contact_id = c.id) JOIN appointments a on (a.id = c_max.max_id) WHERE c.active = 1

[edit] Thanks to Kamikaze I've adjusted to this that get's the first three queries to work, just struggling to get the last one (returns zero, rather than the actual count):

SELECT 
    SUM(CASE WHEN a.appointment < CURDATE() THEN 1 ELSE 0 END) AS "Late",
    SUM(CASE WHEN DATE_FORMAT(a.appointment, "%Y-%m-%d") = DATE_FORMAT(NOW(), "%Y-%m-%d") THEN 1 ELSE 0 END) AS "Today",
    SUM(CASE WHEN a.appointment > CURDATE() + INTERVAL 1 DAY AND a.appointment < CURDATE() + INTERVAL 7 DAY THEN 1 ELSE 0 END) AS "This Week",
    SUM(CASE WHEN c.id NOT IN (SELECT contact_id FROM appointments) THEN 1 ELSE 0 END) AS "Unused"
FROM contacts c JOIN (SELECT MAX(id) max_id, contact_id FROM appointments GROUP BY contact_id) c_max ON (c_max.contact_id = c.id) JOIN appointments a on (a.id = c_max.max_id) WHERE c.active = 1

score 1 · Accepted Answer · answered Dec 30 '19 at 22:09

1

I just happened to answer a very similar question:

The answer is to use a SELECT SUM(CASE WHEN ... END) as col1, SUM(CASE WHEN ... END) as col2, etc where each CASE returns 1 or 0.

answered Dec 30 '19 at 22:09

karmakaze

34,689
1
30
32

If there's a very similar question, just close as a duplicate, don't bother posting an answer. – Barmar Dec 30 '19 at 22:10
The answer is similar but the questions not so much. – karmakaze Dec 30 '19 at 22:11
Thank you, that's a really helpful and get's me three quarters of the way - quite literally, first three queries work, last one doesn't which I think might be to do with the join but not sure how to fix it – Sam Dec 30 '19 at 22:31
1

Ahh, nevermind, I just realised I can reference that in another way, thanks for your help, all sorted – Sam Dec 30 '19 at 23:19

Reducing four MySQL queries to one

1 Answers1