php - Querying records based on join not returning expected results

Solution:

I want to retrieve subscriptions that have expired in the last 6 hours.

Why not just do something like this?

SELECT DISTINCT t.subscription_id
FROM app_transactions t
WHERE t.expiration > now() - interval 6 hour;
Copy code

If expirations can be in the future as well:

SELECT DISTINCT t.subscription_id
FROM app_transactions t
WHERE t.expiration > now() - interval 6 hour AND
      t.expiration < now();
Copy code

EDIT:

Your comment requires a small tweak to this query, basicallyGROUP BY instead ofSELECT DISTINCT:

SELECT t.subscription_id
FROM app_transactions t
WHERE (t.expiration > now() - interval 6 hour AND
       t.expiration < now()
      ) OR
      t.expiration IS NULL
GROUP BY t.sucscription_id
HAVING SUM( t.expiration IS NULL ) = 0;  -- no NULL ones
Copy code

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.