return multiple records as one string in query
mysql
Solution
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..
Problem
``` 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 query ``` 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 would 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 the results like this: ``` last_name sport Robinson basketball, baseball Smith football, baseball Norris baseball, basketball ```