I have two tables in mySql like:
table 1: city
id name transport
1 new-york 1,3,4
2 dallas 3,4
3 la 1,2,4
4 california 3,4
table 2: transport
id name
1 bus
2 trolleybus
3 train
4 metro
Can I received result like example with one query?
result:
id name transport
1 new-york bus,train,metro
2 dallas train,metro
3 la bus,trolleybus,metro
4 california train,metro
You should change your database structure and normalize it. Never store data as comma-separation since its a bad way to store data. However till you fix the database design the following query should do what you are looking at.
select
id,
name,
group_concat(transport)
from
(
select
c.id,
c.name,
t.transport as transport
from city c
join transport t on find_in_set(t.id,c.transport)
)x
group by id ;
If you need to order the transport values then you can use
group_concat(transport ORDER BY transport)
why is comma-separation is bad practice? You can read the following why it should be ignored Is storing a delimited list in a database column really that bad?
To normalize the database you will need to create another table as
city_transport (cid int , tid) ;
cid = city id
tid = transport id
For each city you will have multiple entry in this table. So the tables should look like
create table city (id int , name varchar(100));
insert into city values
(1,'new-york'),(2,'dallas'),(3,'la'),(4,'california');
create table transport (id int ,transport varchar(100));
insert into transport values
(1,'bus'),(2,'trolleybus'),(3,'train'),(4,'metro');
create table city_transport (cid int ,tid int);
insert into city_transport values
(1,1),(1,3),(1,4),(2,3),(2,4),(3,1),(3,2),(3,4),(4,3),(4,4);
And the query to get the same result is as
select
c.id,
c.name,
group_concat(t.transport order by t.transport) as transport
from city_transport ct
join city c on c.id = ct.cid
join transport t on t.id = ct.tid
group by c.id ;
When you have a large amount of data then essentially you will need index and then using join on indexed columns the performance will be way better than using find_in_set with comma separated list
You should work with a table between city and transport to be correct. That being said, you could fix this using REPLACE() and subqueries but the performance will be horrible.
