How can I create a MySQL query within a query and concatenate?

Question

So I have the following table, users:

+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| uid       | int(10) unsigned | NO   | PRI | 0       |       |
| name      | varchar(60)      | NO   | UNI |         |       |
---------------------------------------------------------------

And the table, roles:

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| rid   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(64)      | NO   | UNI |         |                |
+-------+------------------+------+-----+---------+----------------+

I have a third table, users_roles, which in a one-to-many relationship maps users to one or more roles:

+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| uid   | int(10) unsigned | NO   | PRI | 0       |       |
| rid   | int(10) unsigned | NO   | PRI | 0       |       |
+-------+------------------+------+-----+---------+-------+

Finally, I have a fourth table, memberships, that maps one-to-one with users:

+-------------------+---------------+------+-----+---------+-------+
| Field             | Type          | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| membership_no     | varchar(32)   | NO   | PRI | NULL    |       |
| uid               | int(11)       | YES  |     | NULL    |       |
--------------------------------------------------------------------

Currently I have a nice and simple query that provides a list of users with their membership number:

SELECT u.uid, u.name, m.membership_no FROM users AS u 
LEFT JOIN memberships AS m ON u.uid = m.uid;

But now I would like to add an additional column to my query that looks up all the users_roles entries once per user, and then concatenates each roles.name in one column, so I can see all the roles for that user. An example output might be:

uid    name       membership no    roles
---    ----       -----            ------
1      foo        123432           admin, normal user, student
2      bar baz    235235           admin
3      bak        2352352          normal user, student

So my task is how I would integrate this additional query on the users_roles table, link user_roles.rid to roles.rid and concatenate the data in a column. Any pointers?

You can use GROUP_CONCAT, although personally, I prefer to handle array manipulation at the application level (e.g. with a bit of PHP) — Strawberry
– Strawberry, Commented Dec 10, 2013 at 17:34

gen_Eric · Accepted Answer · 2013-12-10 17:38:41Z

4

You're looking for GROUP_CONCAT. First JOIN the other tables, so you can get the role names, then GROUP them together by user id.

SELECT u.uid, u.name, m.membership_no, GROUP_CONCAT(r.name ORDER BY r.rid) AS roles
FROM users AS u 
LEFT JOIN memberships AS m ON u.uid = m.uid
JOIN users_roles AS ur ON u.uid = ur.uid
JOIN roles AS r ON ur.rid = r.rid
GROUP BY u.uid

DEMO: http://sqlfiddle.com/#!2/ffa31/1

edited Dec 10, 2013 at 17:38

answered Dec 10, 2013 at 17:26

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gen_Eric · Accepted Answer · 2013-12-10 17:34:43Z

3

Use MySQL GROUP_CONCAT function.

SELECT 
u.uid, 
u.name, 
m.membership_no,
(SELECT GROUP_CONCAT(name) FROM users_roles ur INNER JOIN roles r ON r.rid = ur.rid WHERE ur.uid = u.uid) as roles
FROM users AS u 
LEFT JOIN memberships AS m ON u.uid = m.uid;

edited Dec 10, 2013 at 17:34

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

answered Dec 10, 2013 at 17:25

beiller

3,1351 gold badge14 silver badges20 bronze badges

2 Comments

gen_Eric Over a year ago

I'd probably prefer a JOIN over a subquery, but this is indeed one solution.

beiller Over a year ago

LOL just cause I said it I got picked. But, I still think it is :)

Collectives™ on Stack Overflow

How can I create a MySQL query within a query and concatenate?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related