CODEWITHSUNDEEP
phpmysqlsqljoin
Jody Heavener
Jody Heavener

Reputation: 2874

Querying records based on join not returning expected results

I am trying to write a query in MySQL that retrieves subscription records, based on the expiration column of joined transactions.

A subscription can have 0 or more transaction records, and transaction.expiration can be NULL or a Datetime. I want to retrieve subscriptions that have expired in the last 6 hours, but only if the subscription doesn't also have a transaction with a NULL expiration (this would mean the subscription does not expire).

Here's my query right now:

SELECT subscription.id
  FROM app_subscriptions AS subscription
  JOIN (
           SELECT subscription_id
             FROM app_transactions
            WHERE (
                        expiration >= %s
                    AND expiration <= %s
                    AND expiration IS NOT NULL
                  )
         GROUP BY subscription_id
       ) as transaction
    ON subscription.id = transaction.subscription_id
 ORDER BY subscription.id DESC

The datetime comparisons are set using PHP (formatted like 0000-00-00 00:00:00)

Testing this against two subscriptions:

Upvotes: 1

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

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;

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

EDIT:

Your comment requires a small tweak to this query, basically GROUP BY instead of SELECT 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

Upvotes: 1

Related Questions