Return multiple records as one row in a query

students: id, last_name

1 Robinson
2 Norris
3 Smith

sports: id, title

1 Basketball 
2 Baseball
3 Football

students_sports: student_id, sport_id

1 3
1 2
2 1
2 3
3 3
3 1

This request

select 
 last_name, sports.title as sport
from 
 students s
left join
 students_sports ss
on
 s.id = ss.student_id
left join
 sports
on
 ss.sport_id = sports.id

It will return something like:

last_name       sport
Robinson        basketball
Robinson        baseball
Smith           football
Smith           baseball
Norris          baseball
Norris          basketball

I want to modify the query to return results as follows:

last_name       sport
Robinson        basketball, baseball
Smith           football, baseball
Norris          baseball, basketball
+3
source share
1 answer

As suggested by @brad:

select 
 last_name, group_concat(sports.title) as sport
from 
 students s
left join
 students_sports ss
on
 s.id = ss.student_id
left join
 sports
on
 ss.sport_id = sports.id
group by s.id

Edit: updated the group by.

+9
source

All Articles