CODEWITHSUNDEEP
mysqlsqldatabasedatejoin
Nonetallt
Nonetallt

Reputation: 371

MySQL join/subquery where results are filtered by multiple values of foreign table

I have two tables:

The user_action table has the following attributes:

I have two different types of actions:

Now my question is: how do I make query such that I will get all the users that have opened a message within the last month but disclude all the users that have received a message within the last 2 days?

I think this can be done with some form of a join and then using WHERE NOT IN with a subquery but that does not sound very effective so I was wondering if there is a better way.

Upvotes: 0

Views: 33

Answers (1)

GMB
GMB

Reputation: 222572

I generally tend to use JOINs instead of EXIST/NOT EXIST subqueries.

Here is an approach using a self-LEFT JOIN :

SELECT DISTINCT
    user_id
FROM
    user_action ua
    LEFT JOIN ua2 
        ON  ua2.user_id = ua.userid
        AND ua2.type = 'received message'
        AND ua2.timestamp > CURRENT_TIMESTAMP() - INTERVAL 2 DAY
WHERE 
    ua.type = 'opened message'
    AND ua.timestamp > CURRENT_TIMESTAMP() - INTERVAL 1 MONTH
    AND ua2.user_id IS NULL

Details :

  • the ua1 searches for users having opened messages within the last month, using the WHERE clause
  • LEFT JOIN ua2 optionnaly tries to find a received message for the same user within the last two days
  • WHERE ... ua2.user_id IS NULL removes records for which a such message happened (ie for which the LEFT JOIN was successful)
  • SELECT DISTINCT avoids duplicated output rows when a given user has more than one received message within the last month

Upvotes: 1

Related Questions