How to select one record from a table with several conditions in one field?

Question

I have a table like this:

--------------------- | id | user | group | --------------------- | 0 | 103 | 27 | | 1 | 107 | 31 | | 2 | 103 | 32 | | 3 | 112 | 31 | | 4 | 135 | 64 | | 5 | 135 | 66 | --------------------- 

This is a table of groups and users, and, as you can see, there can be records with the same user and different groups. How can I choose, for example, records with the same user and two groups - 27 and 32? This will be the same user.

So how can I do this?

EDIT: To show the expected result, I wrote a sample:

 SELECT user FROM table ... (maybe WHERE group = 27 AND group = 32) 

- gives user id 103

EDIT 2: I found it!

Here is a query that works fine for me:

 SELECT * FROM table WHERE `user` in ( SELECT `user` FROM table WHERE `group` = <needed_group_1> ) AND `group` = <needed_group_2> 

Thank you all for your help, I found all your answers helpful!

Answer 1

To get all the data with more than 1 group, do

 select * from your_table where `user` in ( select `user` from your_table group by `user` having count(distinct `group`) >= 2 ) 

To get data that have two specific groups, do

 select * from your_table where `user` in ( select `user` from your_table group by `user` having sum(`group` in (27,32)) >= 2 ) 

Answer 2

 SELECT user FROM theTable GROUP BY user HAVING COUNT(group) = 2 

It may also help you:

 SELECT user, COUNT(*) as cnt, GROUP_CONCAT(group) FROM theTable GROUP BY user 

And if you want to capture all records matching your condition:

 SELECT theTable.* FROM theTable JOIN ( SELECT user FROM theTable GROUP BY user HAVING COUNT(group) = 2 ) AS UsersWithTwoGroups USING(user) 

Answer 3

you almost got it

try it

  SELECT user FROM table1 WHERE id in ( select `group` = 27 AND `group` = 32 from table1) group by user 

NOTE. : group mysql keyword is reserved, so avoid loopbacks

this will give:

  USER 103 

DEMO HERE