mysql - Querying two tables with one query with replacing word -

i have 2 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 received result example 1 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 database structure , normalize it. never store data comma-separation since bad way store data. till fix database design following query should looking at.

select  id, name, group_concat(transport) (   select   c.id,   c.name,   t.transport transport   city c   join transport t on find_in_set(t.id,c.transport)  )x  group id ; 

demo

if need order transport values can use

group_concat(transport order transport) 

why comma-separation bad practice? can read following why should ignored is storing delimited list in database column bad?

to normalize database need create table as

city_transport (cid int , tid) ; cid = city id  tid = transport id 

for each city have multiple entry in table. tables should like

create table city (id int , name varchar(100)); insert city values (1,'new-york'),(2,'dallas'),(3,'la'),(4,'california');  create table transport (id int ,transport varchar(100)); insert transport values (1,'bus'),(2,'trolleybus'),(3,'train'),(4,'metro');  create table city_transport (cid int ,tid int);  insert city_transport values (1,1),(1,3),(1,4),(2,3),(2,4),(3,1),(3,2),(3,4),(4,3),(4,4); 

and query same result as

select c.id, c.name, group_concat(t.transport order t.transport) transport city_transport ct join city c on c.id = ct.cid join transport t on t.id = ct.tid  group c.id ; 

when have large amount of data need index , using join on indexed columns performance way better using find_in_set comma separated list