How can I retrieve the latest subscription for each user?-CodePudding

For example we have 2 tables:

TABLE users WITH FIELDS
    id

TABLE providers WITH FIELDS
    id

TABLE subscriptions WITH FIELDS
    id
    start_date
    provider_id - OneToMany
    user_id - OneToMany

And the following sql statement:

SELECT subscription.id
FROM subscriptions
INNER JOIN providers ON 
    subscriptions.provider_id = providers.id AND
    providers.id = 2
INNER JOIN (
    SELECT user_id, MAX(start_date) AS start_date
    FROM subscriptions GROUP BY user_id
) AS max USING (user_id, start_date)

In result, I want to get for each user the subscription whose provider is (in this case) 2 that has the latest start_date. In this case, we use provider_id 2 but that value is dynamic.

I expected the MAX in the second INNER JOIN to take only the subscriptions whose retailer is 2, but it doesn't, it takes all the subscriptions. Why's that?

Thanks in advance.

CodePudding user response：

Q: I expected the MAX in the second INNER JOIN to take only the subscriptions whose retailer is 2, but it doesn't, it takes all the subscriptions. Why's that?

This subquery with MAX is self-contained. If you execute it as a standalone query:

SELECT user_id, MAX(start_date) AS start_date
    FROM subscriptions GROUP BY user_id

then you would expect it to return each user_id with its max(start_date), independently of the provider_id field, as it is never mentioned in that query.

When it's run as a subquery inside your larger query, well, it returns exactly the same results.

You may just add the condition WHERE provider_id=2 to that subquery.