CODEWITHSUNDEEP
phpmysqldatetimeselect
Andr
Andr

Reputation: 21

MySQL select datetime for expired subscriptions

I have a table called "subscriptions" with these columns:

I would like to select all values that their subscriptions are expired and they don't have any ongoing subscriptions.

For example I have these records in this table:

id | client_id | date | expiring
1 | 23 | 2015-07-07 08:26:08 | 2015-07-27 08:26:08
2 | 23 | 2015-07-10 08:26:08 | 2015-07-15 08:26:08
3 | 21 | 2015-07-14 08:26:08 | 2015-07-15 08:26:08

That means the result should show me client with ID 21 but not the client with ID 23 since he has an active subscription.

I hope i explained it well.

Thanks in advance

Upvotes: 1

Views: 2848

Answers (1)

pierroz
pierroz

Reputation: 7870

SELECT * FROM subscriptions 
WHERE client_id NOT IN (
    SELECT client_id FROM subscriptions 
    WHERE expiring > NOW())

Upvotes: 3

Related Questions