Hello Everyone,


Is there any MySQL statement which i can fire and get result like this, using tables 'users' and 'comma'.

Desired Output:
-------------------------------------
id | user | joint_result
-------------------------------------
1 | user1 | a1,a2,a3,a4
2 | user2 | b1,b2,b3,b4
3 | user3 | c1,c2,c3,c4,c5
-------------------------------------

I would really appreciate any clue regading this.

Thanks everyone in advance. table contents and sql is below.


mysql> select * from users;
+----+-------+
| id | user |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
+----+-------+
3 rows in set (0.00 sec)


mysql> select * from comma;
+----+-------+------+
| id | user | data |
+----+-------+------+
| 1 | user1 | a1 |
| 2 | user1 | a2 |
| 3 | user1 | a3 |
| 4 | user2 | b1 |
| 5 | user2 | b2 |
| 6 | user2 | b3 |
| 7 | user3 | c1 |
| 8 | user3 | c2 |
| 9 | user3 | c3 |
| 10 | user3 | c4 |
| 11 | user1 | a4 |
| 12 | user2 | b4 |
| 13 | user3 | c5 |
+----+-------+------+
13 rows in set (0.02 sec)


_________________________________________

tables sql :

CREATE DATABASE IF NOT EXISTS my_DB;
USE my_DB;
CREATE TABLE `my_DB`.`comma` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user` varchar(45) DEFAULT NULL,
`data` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
INSERT INTO `my_DB`.`comma` (`id`,`user`,`data`) VALUES
(1,'user1','a1'),
(2,'user1','a2'),
(3,'user1','a3'),
(4,'user2','b1'),
(5,'user2','b2'),
(6,'user2','b3'),
(7,'user3','c1'),
(8,'user3','c2'),
(9,'user3','c3'),
(10,'user3','c4'),
(11,'user1','a4'),
(12,'user2','b4'),
(13,'user3','c5');
CREATE TABLE `my_DB`.`users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
INSERT INTO `my_DB`.`users` (`id`,`user`) VALUES
(1,'user1'),
(2,'user2'),
(3,'user3');

Use MySQL's group_concat function. The code is not difficult and I'd write it for you but it looks too much like homework for me.

Mike is right...

but I could use the practice... so here is the homework:

select b.id, a.user, group_concat(a.data) as Data
from comma a
join users as b on a.user = b.user


should work for you!

many thanks to mike_bike_kite and dockraj


it worked for me....