CODEWITHSUNDEEP
mysql
madhu
madhu

Reputation: 139

query to retrieve data from mysql in list of maps

I am trying to write a query in MYSQL . I have a table called student_data. It has 4 columns.

student_data_id
student_id
course_name
registerd_time

I am trying to retrieve courses registered for each student. I used the following query.

select 
    student_id,course_name from student_data 
where 
    student_id IN(2224508,22260211) and 
    course_name IN('Java','Salesforce','Unix','VBScript') and 
    registered_time > NOW() - INTERVAL 1 DAY 
group by student_id,course_name;

I am getting the following the output:

2224508     Java
2224508     Salesforce
2224508     Unix
22260211    Java
22260211    Salesforce
22260211    VBScript

I am trying to get the output in this format:

2224508     Java,Salesforce,Unix
22260211    Java,Salesforce,VBScript

How to write a query or JDBC template to get the above format?

Upvotes: 0

Views: 374

Answers (3)

Raymond Nijland
Raymond Nijland

Reputation: 11602

The function GROUP_CONCAT concats a GROUP (in this case student_id) with a default separator , 

SET SESSION group_concat_max_len = @@max_allowed_packet

select 
     student_id
   , GROUP_CONCAT(course_name)
from student_data 
where 
    student_id IN(2224508,22260211) and 
    course_name IN('Java','Salesforce','Unix','VBScript') and 
    registered_time > NOW() - INTERVAL 1 DAY 
group by student_id;

Upvotes: 2

Ike Walker
Ike Walker

Reputation: 65547

You can accomplish this using GROUP_CONCAT():

select 
    student_id,group_concat(course_name) 
from student_data 
where 
    student_id IN(2224508,22260211) and 
    course_name IN('Java','Salesforce','Unix','VBScript') and 
    registered_time > NOW() - INTERVAL 1 DAY 
group by student_id;

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Try this - 

select student_id
      ,GROUP_CONCAT(course_name) 
from student_data 
where student_id IN(2224508, 22260211) 
and course_name IN('Java','Salesforce','Unix','VBScript') 
and registered_time > NOW() - INTERVAL 1 DAY 
group by student_id;

Upvotes: 1

Related Questions