0

I have the following problem: I have three tables, a users table, a categories table and a user_category table which connects the two first tables with foregn keys.

users table:

id     username

1      user1
...    ...

categories table:

id    category

1     test1
2     test2
3     test3
...   ...

user_category table

id    user_id    category_id
1     1          1
2     1          2
3     1          3

Now I want to select all users with their categories, but I dont want to have multiple rows of the same user - instead, I want to merge all categories of a user into one category field.

SELECT users.*, categories.category FROM user_category INNER JOIN users ON users.id = user_category.user_id LEFT JOIN categories ON categories.id = user_category.category_id

The output:

 id     username     category
 1      user1        test1
 1      user1        test2
 1      user1        test3

But I want the following:

 id     username     category
 1      user1        test1, test2, test3

Is there a possibility to do this?

pete
  • 199
  • 3
  • 15

1 Answers1

2
SELECT users.id, users.username, GROUP_CONCAT(categories.category) as cats
FROM user_category 
INNER JOIN users ON users.id = user_category.user_id 
LEFT JOIN categories ON categories.id = user_category.category_id
GROUP BY users.id, users.username

could do what you want.

See http://www.sqlines.com/mysql/functions/group_concat

With TSQL you can use smth like How Stuff and 'For Xml Path' work in Sql Server

Patrick Artner
  • 50,409
  • 9
  • 43
  • 69
  • The problem is, that I just get one category for the user instead of all. – pete Nov 10 '17 at 19:52
  • 2
    @pete It does work, here you have a working example: http://rextester.com/MTZROD6195 *Note: I had to change `users` to `usersx` so it would run on rextester* – Piyin Nov 10 '17 at 20:20
  • Thanks a lot! I don't know what was wrong, but it works now. – pete Nov 10 '17 at 20:41