CODEWITHSUNDEEP
mysql
njp
njp

Reputation: 705

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

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?

Upvotes: 0

Views: 55

Answers (2)

gen_Eric
gen_Eric

Reputation: 227200

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

Upvotes: 4

beiller
beiller

Reputation: 3135

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;

Upvotes: 3

Related Questions