Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Exclamation Unanswered: Simple logic but clueless .........

    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');

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  3. #3
    Join Date
    Mar 2010
    Posts
    16
    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!

  4. #4
    Join Date
    Mar 2010
    Posts
    2

    many thanks to mike_bike_kite and dockraj

    many thanks to mike_bike_kite and dockraj


    it worked for me....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •