0

Table 1: users

id name
1  name 1
2  name 2

Table 2: user_city

user_id city_id
1        1
1        2
2        1
2        2

Table 3: city

id name_city
1  HCM
2  Ha Noi
3  DA NAng

Sql of me:

select a.*,c.name_city 
FROM users as a 
INNER JOIN 
user_city as b 
ON b.user_id = a.id 
INNER JOIN city as c 
ON c.id = b.city_id

Results:

id  name  name_city

1  name 1 HCM
2  name 2 Ha Noi
1  name 1 Ha Noi
2  name 2 HCM

I want result is:

Results:

id name name_city

1  name 1 HCM,Ha Noi
2  name 2 HCM,Ha Noi
radar
  • 13,270
  • 2
  • 25
  • 33
Thành Lê
  • 21
  • 4
  • possible duplicate of [How to use GROUP BY to concatenate strings in MySQL?](http://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql) – Pரதீப் Oct 23 '14 at 04:31

1 Answers1

2

you can use GROUP_CONCAT AND GROUP BY to get the result

SELECT U.id, U.name, GROUP_CONCAT(c.name_city)
FROM users u
join user_city uc
on u.id = uc.user_id
join city c
on uc.city_id = c.id
group by u.id, u.name
radar
  • 13,270
  • 2
  • 25
  • 33