MySQL WHERE IN

Question

My query is    SELECT   FROM table WHERE id IN  1 2 3 4    I use it for usergroups and a user can be in more than one group  but it seems that when a record has multiple id like 1 and 3  mySQL does not return that row   Is there any way to get that row too

User · Answer

you must have record in table or array record in database  example  SELECT   FROM tabel record WHERE table record fieldName IN  SELECT fieldName FROM table reference

User · Answer

Your query translates to  SELECT   FROM table WHERE id  1  or id  2  or id  3  or id  4     It will only return the results that match it     One way of solving it avoiding the complexity would be  chaning the datatype to SET   Then you could use  FIND IN SET  SELECT   FROM table WHERE FIND IN SET  1   id

User · Answer

You have wrong database design and you should take a time to read something about database normalization  wikipedia   stackoverflow    I assume your table looks somewhat like this  TABLE                                    group id   user ids   name     --------------------------------   1          1 4 6      group1   --------------------------------   2          4 5 1      group2         so in your table of user groups  each row represents one group and in user ids column you have set of user ids assigned to that group   Normalized version of this table would look like this  GROUP                         id         name     ---------------------   1          group1   ---------------------   2          group2        GROUP USER ASSIGNMENT                          group id   user id   ----------------------   1          1         ----------------------   1          4         ----------------------   1          6         ----------------------   2          4         ----------------------               Then you can easily select all users with assigned group  or all users in group  or all groups of user  or whatever you can think of  Also  your sql query will work      Your query to select assignments    SELECT   FROM  group user assignment  WHERE user id IN  1 2 3 4       Select only some users    SELECT   FROM  group user assignment  t1 JOIN  group  t2 ON t2 id   t1 group id WHERE user id IN  1 4       Select all groups of user    SELECT   FROM  group user assignment  t1 JOIN  group  t2 ON t2 id   t1 group id WHERE t1  user id    1      Select all users of group    SELECT   FROM  group user assignment  t1 JOIN  group  t2 ON t2 id   t1 group id WHERE t1  group id    1      Count number of groups user is in    SELECT COUNT    AS  groups count  FROM  group user assignment  WHERE  user id    1      Count number of users in group    SELECT COUNT    AS  users count  FROM  group user assignment  WHERE  group id    1    This way it will be also easier to update database  when you would like to add new assignment  you just simply insert new row in group user assignment  when you want to remove assignment you just delete row in group user assignment    In your database design  to update assignments  you would have to get your assignment set from database  process it and update and then write back to database   Here is sqlFiddle to play with

[mysql] MySQL WHERE IN ()

Examples related to mysql