MySQL Group and Concat multiple columns

Question

I have to add two or more strings from MySQL database, for this I used CONCAT() function.

Here is the first table classes which stores PHP classes.

class_id  class_name
--------  ----------
       1  accountant
       2  attendance

Another table methods which stores each class methods.

class_id  method_name            
--------  -----------------------
       1  __construct            
       1  add_expenses

       2  __construct            
       2  attendance_report

And I write the query for concatenation.

SELECT 
  `cc`.`class_id`,
  `cc`.`class_name`,
  CONCAT(`cm`.`method_name`, ',') AS `method_name` 
FROM
  `classes` AS `cc` 
  LEFT JOIN `methods` AS `cm` 
    ON `cm`.`class_id` = `cc`.`class_id` 
GROUP BY `cc`.`class_name`;

Which is not working. My expected output is

class_id  class_name      method_name 
--------  --------------  ------------
       1  accountant      __construct, add_expenses, .... n
       2  attendance      __construct, attendance_report, .... n

Any ideas?

Shakti Singh · Accepted Answer · 2011-06-30 12:54:06Z

3

Use

GROUP_CONCAT

instead of

CONCAT

GROUP_CONCAT(cm.method_name) you do not needs to pass comma as separator that will be taken default.

edited Jun 30, 2011 at 12:54

answered Jun 30, 2011 at 12:46

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Group and Concat multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related