Oh What A Noob Oh What A Noob - 2 months ago 7
MySQL Question

Joining two table one to many relationship and then concatenate the second table into one

I have two tables

users table
id name
1 john
2 reaper


users_bid table
id user_id amount
1 1 50
2 1 100
3 2 80
4 1 120


Now i want a query which will give the below result

id user_id amount
1 1 50,100,120
2 2 80

Answer

You want GROUP_CONCAT()

select T1.id, T2.user_id, group_concat(T2.amount order by T2.amount separator ', ') as amount
from users T1
inner join users_bid T2
on T2.user_id = T1.id
group by T1.id, T2.user_id