MySQL selecting specific entries from joined table

Question

I have a table with users' subscriptions to a specific service, billed monthly. There is also a table of 'suspensions' - with 'from' and 'to' dates to store information about user's temporary suspension of his subscription.

There are three possible cases:

• both 'from' and 'to' dates are in the past - the user had its subscription suspended for a period in the past, but now the subscription is active again
• 'from' is in the past, but 'to' is in the future or is NULL - the service is suspended at the moment (until 'to' date or undefinitely)
• 'from' and 'to' are in the future (or 'from' is in the future and 'to' is NULL) - the user requested a future subscription suspension/cancellation, but at the moment the service is still active

There may be multiple entries in 'suspensions' for a given subscription, some may be already done (in the past), some 'in progress'.

Now I need to select active subscriptions.

I tried to do someting like:

SELECT * FROM service 
LEFT JOIN suspensions ON service.id = suspensions.service_id 
WHERE 
  suspensions.id IS NULL 
OR 
  ( suspension_from < CURDATE() AND suspension_to < CURDATE() ) 
OR ( suspenstion_from > CURDATE() AND ( suspension_to > CURDATE() OR suspension_to IS NULL)) ) 
GROUP BY 
   service.customer_id

and that works if there's only one entry in 'suspensions' table for a given service.

But if a customer has multiple suspension entries with different outcomes as to present moment, for example from 01-01-2021 to 31-12-2021 and 01-09-2023 to 31-08-2024 then the WHERE clause treats the first one as "active service" (suspension in the past), the second one as "inactive at the moment" (and omits that one as expected) and in the result I have that service as active (because of the first entry), while in fact it is inactive.

How to do this? Somehow count (or concat) "active" results and "inactive" results, group by customer_id and afterwards filter only those, which have 0 inactive results?

Answer 1

SELECT * 
FROM service 
LEFT JOIN suspensions ON service.id = suspensions.service_id 
WHERE suspensions.service_id IS NULL 
   OR CURRENT_DATE NOT BETWEEN suspension_from AND COALESCE(suspension_to, CURRENT_DATE) 
-- GROUP BY service.customer_id
-- maybe, ORDER BY service.customer_id ?