[RESOLVED] MySQL Group_Concat issue

I have 3 tables that I join together and on the 3rd table out I'd like the values in a column to be concat together into a single column in the end result. Here are the tables I have:

Users:

Code:

---

+--------+--------------------+-----------+----------+
| UserID | EmailAddress      | FirstName | LastName |
+--------+--------------------+-----------+----------+
|      1 | [email protected]  | Author    | One      |
|      2 | [email protected]  | Author    | Two      |
|      3 | [email protected]  | Author    | Three    |
|      4 | [email protected] | Reviewer  | One      |
|      5 | [email protected] | Reviewer  | Two      |
|      6 | [email protected]  | Editor    | One      |
|      7 | [email protected]  | All      | Roles    |
+--------+--------------------+-----------+----------+

---

UserRoles:

Code:

---

+--------+--------+
| UserID | RoleID |
+--------+--------+
|      1 |      1 |
|      2 |      1 |
|      2 |      2 |
|      3 |      1 |
|      4 |      1 |
|      4 |      2 |
|      5 |      1 |
|      5 |      2 |
|      6 |      1 |
|      6 |      3 |
|      7 |      1 |
|      7 |      2 |
|      7 |      3 |
+--------+--------+

---

Roles:

Code:

---

+--------+-------------------+
| RoleID | RoleTitle        |
+--------+-------------------+
|      1 | Author            |
|      2 | Reviewer          |
|      3 | Editor            |
|      4 | Assistant Editor  |
|      5 | General Assistant |
|      6 | Public            |
+--------+-------------------+

---

What I would like the output to be is:

Code:

---

+--------+--------------------+---------------+------------------------+----------+
| UserID | EmailAddress      | FullName      | Roles                  | IsActive |
+--------+--------------------+---------------+------------------------+----------+
|      1 | [email protected]  | One, Author  | Author                | Y        |
|      2 | [email protected]  | Two, Author  | Author                | Y        |
|      3 | [email protected]  | Three, Author | Author                | N        |
|      4 | [email protected] | One, Reviewer | Author,Reviewer        | Y        |
|      5 | [email protected] | Two, Reviewer | Author,Reviewer        | Y        |
|      6 | [email protected]  | One, Editor  | Author,Reviewer,Editor | Y        |
|      7 | [email protected]  | Roles, All    | Author,Reviewer,Editor | Y        |
+--------+--------------------+---------------+------------------------+----------+

---

Instead what I'm getting is:

Code:

---

+--------+--------------------+---------------+----------------------------------------------------------------------------------------------------+----------+
| UserID | EmailAddress      | FullName      | Roles                                                                                              | IsActive |
+--------+--------------------+---------------+----------------------------------------------------------------------------------------------------+----------+
|      1 | [email protected]  | One, Author  | Author,Author,Reviewer,Author,Author,Reviewer,Author,Reviewer,Author,Editor,Author,Reviewer,Editor | Y        |
+--------+--------------------+---------------+----------------------------------------------------------------------------------------------------+----------+

---

Which appears to be everything in that column jammed into a single row. My query is:

Code:

---

Select u.UserID,
      u.EmailAddress,
      CONCAT(u.LastName,', ',u.FirstName) As 'FullName',
      GROUP_CONCAT(r.RoleTitle) As 'Roles',
      IF(u.Active, 'Y', 'N') As 'IsActive'
From Users u
  Inner Join UserRoles ur
    On ur.UserID = u.UserID
  Inner Join Roles r
    On r.RoleID = ur.RoleID
Order By u.UserID;

---

Any ideas why the Group_Concat isn't grouping correctly?

Database 101: when using an aggregate function, be sure to put all non-aggregated fields into a Group By in the query, I'd neglected to do that. My query is now:

Code:

---

Select u.UserID,
      u.EmailAddress,
      CONCAT(u.LastName,', ',u.FirstName) As 'FullName',
      GROUP_CONCAT(r.RoleTitle) As 'Roles',
      IF(u.Active, 'Y', 'N') As 'IsActive'
From Users u
  Inner Join UserRoles ur
    On ur.UserID = u.UserID
  Inner Join Roles r
    On r.RoleID = ur.RoleID
Group By u.UserID,
        u.EmailAddress,
        u.FirstName,
        u.LastName,
        u.Active
Order By u.LastName,
        u.FirstName,
        u.UserID;

---

And it works just fine.