I have following structures:

CREATE TABLE `proxies` (
  `proxy_id` int(11) NOT NULL AUTO_INCREMENT,
  `proxy_ip` varchar(50) DEFAULT NULL,
  `proxy_port` smallint(5) unsigned DEFAULT NULL,
  `proxy_country` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`proxy_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `accounts` (
  `account_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `ip_range` text,
  PRIMARY KEY (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And I have 3 rows in proxies table with country names of Germany, Finland and France. The ip_range Field in accounts tables holds list of proxy_ids separated by , character.

Now I have a query like this:

SELECT accounts.*, GROUP_CONCAT(proxy_country) countries
FROM accounts
LEFT JOIN `proxies` ON `proxy_id` IN (`ip_range`)
GROUP BY `account_id`

I expected something like this:

But what i get is:

As i noticed, ip_range Will cast as text so my query is something like

SELECT accounts.*, GROUP_CONCAT(proxy_country) countries
FROM accounts
LEFT JOIN `proxies` ON `proxy_id` IN ('1,2,3')
GROUP BY `account_id`

That is wrong. it should be:

SELECT accounts.*, GROUP_CONCAT(proxy_country) countries
FROM accounts
LEFT JOIN `proxies` ON `proxy_id` IN (1,2,3)
GROUP BY `account_id`

How can I fix it?

>Solution :

For IN you need single values, for searching strings You use FIND_IN SET like below

SELECT accounts.*, GROUP_CONCAT(proxy_country) countries
FROM accounts
LEFT JOIN `proxies` ON FIND_IN_SET(`proxy_id` , `ip_range`)
GROUP BY `account_id`

But you should read Is storing a delimited list in a database column really that bad? and normalize your table structure, every string function is slow.