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.