CODEWITHSUNDEEP
mysql
OPK
OPK

Reputation: 345

MySql IN Operator on a string

SELECT * FROM users WHERE uid IN (SELECT doctors FROM MainPage WHERE Valid=1)

users table uid datatype=INT, Mainpage table doctors datatype=text with value as 1,2,3,4,5

When I am running the above query, it is only resulting 1 row which is for uid=1. Is there any other operator OR query change which can retreive me all 5 rows from users table ?

Upvotes: 0

Views: 1928

Answers (1)

shmosel
shmosel

Reputation: 50716

The FIND_IN_SET() function searches for a value in a comma-delimited list. Combine that with a JOIN and you should get what you want:

SELECT *
  FROM users
  JOIN MainPage ON FIND_IN_SET(uid, doctors)
 WHERE Valid = 1;

FYI, this is the wrong way to design your tables. Each connection should be defined on a separate row. If it's a many-to-many relationship, that will require a separate table with a composite key.

Upvotes: 4

Related Questions