add row values into single column MYSQL

Question

I have this data.

 create table student
(
   student_id int,
   name varchar(50)
 )

 create table student_option
(
   student_option_id int, 
   student_id int,
   s_option  varchar(50)
 )  
 insert into student
(
student_id,
name
)values(1,'John'),(2,'Martin')

insert into student_option
(
  student_option_id,
  student_id,
  s_option
  )values(1,1,'A'),(2,1,'B'),(3,2,'C'),(4,2,'D')

select * from student 
select * from student_option

I want the output to be displayed as

  student_name     options
    John             A, B
    Martin           C, D

what is the most optimized way to do this in MYSQL5.6

Thanks in advance

Answer 1

This one should work. You basically use GROUP_CONCAT function from MySQL and GROUP BY name after.

select s.name, group_concat(o.s_option) 
  from student s 
  left join student_option o on (s.student_id = o.student_id) 
  group by s.name;

Result:

name    s_option

John    B,A    
Martin  D,C