Mysql query with group

Question

I have the following table:

id  club_id club_option_id created
---|-------|--------------|-------
1  | 1     | 2            | 2015-02-11 16:31:23
2  | 1     | 3            | 2015-02-11 16:31:23
3  | 2     | 2            | 2015-03-06 08:16:02

I would like to select the club (club_id) who has both options (2 and 3)

I don't get any results with the following query:

SELECT club_id FROM 
club_options_to_clubs 
WHERE club_option_id = 2 AND club_option_id = 3  
GROUP BY club_id

How can I get club_id 1 as a result?

Answer 1

You can do as below -

select 
t1.club_id from table_name t1
where t1.club_option_id = 2
and exits (
  select 1 from table_name t2
  where t1.club_id = t2.club_id
  and t2.club_option_id = 3
)

The other way is

select club_id from table_name
where club_option_id in (2,3)
group by club_id 
having count(*) = 2

With the above approach if you need to check multiple club_option_id pass them in the in function and then use the number in having count(*) = n Here n = number of items in the in function.