CODEWITHSUNDEEP
mysqlsql
Oh What A Noob
Oh What A Noob

Reputation: 507

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

Upvotes: 0

Views: 63

Answers (2)

JohnHC
JohnHC

Reputation: 11205

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

Upvotes: 2

Muhammad Suhaib
Muhammad Suhaib

Reputation: 90

SELECT users.id, users_bid.user_id, users_bid.group_concat(amount) AS amount
FROM users
JOIN users_bid ON users_bid.user_id = users.id
GROUP BY users.id

Upvotes: 1

Related Questions