SELECT categories from a mapping table

Question

I have 3 tables :

categories:
ID, category
"1","Cars"
"2","Trucks"
"3","Bikes"
"4","Planes"
"5","Boats"

users:
ID, username
"1","john"
"2","bob"
"3","billy"

users_categories:
ID, userid, categoryid
"1","1","2"
"2","1","5"
"3","2","3"
"4","3","2"
"5","3","4"
"6","3","5"

Q1. What I want is :

john,Trucks,Boats
bob,Bikes
billy,Trucks,Planes,Boats

I've come to this. A Concat of the categories would do.

SELECT U.`username`, (SELECT C.`category` FROM `categories` C LEFT JOIN `users_categories` UC ON C.`ID` = UC.`categoryid` WHERE U.ID = UC.userid) FROM  `users` U

But I get #1242 - Subquery returns more than 1 row.

Q2. Is there a better way to structure this ? There won't be more than 50-100 categories.

Answer 1

use GROUP_CONCAT to achieve what you want

SELECT  a.username,
        GROUP_CONCAT(c.category)
FROM    users a
        INNER JOIN users_categories b
            On a.Id = b.userID
        INNER JOIN categories c
            ON b.categoryID = c.ID
GROUP BY a.ID

• SQLFiddle Demo