Hello. I suffer the second day.

Suppose there is a table of users with the id and name fields (id is unique), and there is a linked table linking these users with groups. In this table, the user_id and group_id fields. You need one request to get users who are simultaneously in group 6 and group 12.

It seems simple, but something does not work. :( I could only get all the users who are in group 6, and all those who are in group 12, and then filter the extra code, but in view of a very large amount of data - the sample takes 7 seconds, which is extremely indecent for a long time ... I will grateful for any ideas ...

  • one
    @Etki kindly translate the comment in response. @HukpoFuJl if one of the answers is correct, please do not be too lazy to mark this answer as correct. - smackmychi 4:34

2 answers 2

 SELECT u.id, u.name FROM users AS u INNER JOIN relations AS r1 ON r1.user_id = u.id AND r1.group_id = 6 INNER JOIN relations AS r2 ON r2.user_id = u.id AND r2.group_id = 12
  • Thanks, I 'll test it now ... - HukpoFuJl
  • Strange, but in my case it finds 0 records = ((( - HukpoFuJl
  • Although, if you simulate the situation that I described here, then everything works ... Ok, I will dance from that, thanks - HukpoFuJl

Try this

 where group_id in(6,12)