CODEWITHSUNDEEP
mysqlsqlinner-join
Thành Lê
Thành Lê

Reputation: 21

Mysql inner join and filter result

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

Upvotes: 0

Views: 102

Answers (1)

radar
radar

Reputation: 13425

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

Upvotes: 2

Related Questions