Mysql – Query three tables result into one row

MySQL

Table 1 – users

------------
id  | name
------------
 1  | foobar
 2  | barfoo

Table 2 – locations

-------------
id | loc_name
-------------
 1 | Location1
 2 | Location2
 3 | Location3
 4 | Location4

Table 3 – users_location

-----------------
user_id | loc_id
-----------------
 1      | 3
 1      | 4
 2      | 1
 2      | 2

The output that I'm looking for is this one

-------------------------------
name    | loc_name
-------------------------------
 foobar | Location3,Location4
 barfoo | Location1,Location2

Currently here is the code I got 

SELECT users.name,locations.loc_name
FROM users
JOIN users_location ON users_location.user_id = users.id
JOIN locations ON locations.id = users_location.loc_id

Here is my current output:

----------------------
 name   | loc_name  
----------------------
 foobar | Location3
 foobar | Location4
 barfoo | Location1
 barfoo | Location2

Best Answer

You can use GROUP_CONCAT to get your expected result:

SELECT US.name, GROUP_CONCAT(LO.loc_name) AS loc_name
FROM users AS US
JOIN users_location AS UL ON UL.user_id = US.id
JOIN locations AS LO ON LO.id = UL.loc_id
GROUP BY US.name;

Also you can use table alias users AS US, for better readability.

As per the comments, for any separator other than comma, you may use [SEPARATOR str_val] also ORDER BY will ensure the expected output in the ascending or descending order.

Sample code for hyphen as separator: 

GROUP_CONCAT(LO.loc_name ORDER BY LO.loc_name SEPARATOR '-') AS loc_name
Related Question