How to use properly IN clause getting the value from another column in MySQL?

Question

In one table I have a list of cities and in another a list of clients. On clients I have a varchar column identifiend a list of cities separated by commas (ex: "2,3,4").

When I tried to list the cities of a client it is shown just the first city of the list. It seem that is adding some quotation marks on the value like:

select GROUP_CONCAT(city.name) from city where city.id_city in ('2,3,4')

¿How can avoid this situacion?

https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=a70c667e820c3208053b324075b0462c

CREATE TABLE `city` (
  `id_city` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id_city`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `client` (
  `id_client` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `cities` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id_client`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO city (id_city,name) VALUES ('1','New York');
INSERT INTO city (id_city,name) VALUES ('2','Boston');
INSERT INTO city (id_city,name) VALUES ('3','San Diego');
INSERT INTO city (id_city,name) VALUES ('4','Seatle');
INSERT INTO city (id_city,name) VALUES ('5','Chicago');

INSERT INTO client (id_client,name,cities) VALUES ('1','Client_1','2,3,4');

select client.id_client, client.name, (select GROUP_CONCAT(city.name) 
from city where city.id_city in (client.cities)) as cities from client;

Does this answer your question? [MySQL query finding values in a comma separated string](https://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string) — Ankit Bajpai, May 05 '21 at 16:21

Ankit Bajpai · Accepted Answer · 2021-05-05T16:31:37.227

You cannot directly pass the list to your queries. You need to change your code to -

SELECT client.id_client,
       client.name,
       (SELECT GROUP_CONCAT(city.name) 
          FROM city
         WHERE FIND_IN_SET(city.id_city, client.cities) <> 0) AS cities
  FROM client;

DB Fiddle.

Though this solves your purpose, I think you must consider visiting this link which clearly says that storing the comma-separated values is really a very bad idea.

How to use properly IN clause getting the value from another column in MySQL?

1 Answers1