MySQL request WHERE ... IN ...

Question

Hi,

in a table "partners", i've a field "sites" which can contain values like 1,27,38,12

then, in a website which has ID n°27, i would like to get partners associated to this website.

I tried this : SELECT * FROM partners WHERE 27 IN (partners.sites)

It works if 27 is at the beginning of the string (eg: 27,1,128) but it doesn't work if 27 is in the middle (eg: 1,27,38,12)

Have you got any idea to manage this ?

Thanks. Cyril

Answer 1

see the manual for find_in_set

Answer 2

SELECT * FROM partners WHERE partners.sites like '%27%'

Answer 3

This doesn't make any sense

Why not make select * from partners where sites=27?

Or are you suggesting that sites is a varchar containing CSV? In this case this is totally wrong from any perspective. Do a one-to-many relationship in your database.

Answer 4

Assuming sites is a field in the same table you're querying, you could try this:

SELECT * FROM partners WHERE sites LIKE %27;

SELECT * FROM partners WHERE sites LIKE 27;

Does that work?

Answer 5

You may want to use the FIND_IN_SET() function, because the IN() function will not expect a comma-separated string as an argument.

This does not work:

SELECT 27 IN ('1,27,5');
+------------------+
| 27 IN ('1,27,5') |
+------------------+
|                0 |
+------------------+

This works:

SELECT FIND_IN_SET(27, '1,27,5') > 0;
+-------------------------------+
| FIND_IN_SET(27, '1,27,5') > 0 |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)

Answer 6

I would have to agree that using relationships will not only be better practice, but will optimize your database request speeds as well, even if it's non noticeable, every bit counts.

So assuming you had a separate table called sites, you could do a call like follows:

SELECT * FROM partners WHERE pid IN (SELECT spid FROM sites WHERE siteid = 27);

Your relationship could then be something like:

-------------------------------------
PARTNERS
-------------------------------------
pid       | some field |
2         |            |
-------------------------------------

-------------------------------------
SITES
-------------------------------------
spid      | siteid     | surl
2         | 27         | http://...
-------------------------------------