MySQL AND operator?

Question

I have a problem with a sql query. I have a table like this:

user_id  exception
  2        3
  2        4 

I want to select the user_id's which exists with the exception 3 AND 4

SELECT user_id 
FROM table 
WHERE exception = 3 
  AND exception = 4 

doesn't work.

Answer 1

Use aggregation and having:

SELECT user_id 
FROM table 
WHERE exception in (3, 4)
GROUP BY user_id
HAVING COUNT(DISTINCT exception) = 2; 

This returns users who have both exceptions. If there are no duplicates for users/exceptions, then use HAVING COUNT(*) = 2.

Answer 2

use exists

  select t1.* from table_name t1
  where exists ( select 1 from table_name t2 where t1.user_id=t2.user_id
                                            where exception in (3,4)
                                            having count(distinct exception)=2)