2

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

1 Answer 1

1

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
4
  • 1
    I am deleting my answer (dba.stackexchange.com/a/233433/877) and upvoting yours since they are essentially the same. Mar 28, 2019 at 19:15
  • Thanks it worked. Just a question I'm thinking is there a way to change the commas between the locations, Like a hypen or something? Mar 28, 2019 at 19:28
  • Yes. Can you please try GROUP_CONCAT(LO.loc_name SEPARATOR '-')
    – Arulkumar
    Mar 28, 2019 at 19:30
  • 1
    The ORDER BY might also be useful for clarity?
    – Vérace
    Mar 28, 2019 at 19:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.