Ozzone Ozzone - 1 month ago 13
MySQL Question

expolde field in query

I have two tables in MySQL.

CREATE TABLE IF NOT EXISTS `city` (
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city` varchar(50) NOT NULL,
PRIMARY KEY (`city_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;


INSERT INTO `city` (`city_id`, `city`) VALUES
(1, 'London'),
(2, 'Moscow'),
(3, 'Perth'),
(4, 'tokyo'),
(5, 'seoul');


CREATE TABLE IF NOT EXISTS `user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) NOT NULL,
`cities_travel` varchar(50) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


INSERT INTO `user` (`user_id`, `user_name`, `cities_travel`) VALUES
(1, 'todd', '5'),
(2, 'martin', '1,2,4');


I am trying to display as follows,

1    todd    seoul

2    martin    London,Moscow,Tokyo

How can I build query so that I will get above our result?

Answer

ya, better way is to make a separate table "usercitytrans" with feids

id (auto increment)
user_id 
city_id

and then save the data in this transaction table. so you may have data like

id  user_id  city_id
1   1        5
2   1        4
3   1        2
4   2        5
5   1        1

in this case, you can get the comma separated cities with this sql

     select u.user_id, user_name, 
    group_concat(city_name separator  ', ' ) as usercities   from (user as u left join usercitytrans as ut
            on u.user_id = ut.user_id) left join city as c
            on ut.city_id = c.city_id
where user_id = 1
            group by ut.city_id