Select record if values exists for same user_id

Question

I need to get records from a mysql database; in this table there are a list of action associated to users. I need to get all users that have column action = 3 and column action = 5; for example if user nr. 4 has a record with action = 3 and another with action = 5 I get it; if user has only one record with action = 3 or action = 5 I mustn't get it. If I write this query

SELECT * 
FROM user_action 
WHERE action = 3 AND action = 5

I always get 0 records. IF i write OR instead of AND I get all records that have action = 3 or action = 5 even if a user doesn't have both value . How ca I do?

Answer 1

This problem is somewhat called Relational Division

SELECT  userID
FROM    user_action
WHERE   action IN (3,5)
GROUP   BY userID
HAVING  COUNT(DISTINCT action) = 2

• SQL of Relational Division

if action is unique for every userID, you can directly COUNT() the records without DISTINCT

SELECT  userID
FROM    user_action
WHERE   action IN (3,5)
GROUP   BY userID
HAVING  COUNT(*) = 2

and lastly, if you want to get the whole details of the user, you need to join it with itself,

SELECT  a.*
FROM    user_action a
        INNER JOIN
        (
            SELECT  userID
            FROM    user_action
            WHERE   action IN (3,5)
            GROUP   BY userID
            HAVING  COUNT(*) = 2
        ) b ON a.userID = b.userID