[RESOLVED] MySQL Group_Concat issue

[JuggaloBrotha]

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:

+--------+--------------------+-----------+----------+
| 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:

+--------+--------+
| 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:

+--------+-------------------+
| 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:

+--------+--------------------+---------------+------------------------+----------+
| 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:

+--------+--------------------+---------------+----------------------------------------------------------------------------------------------------+----------+
| 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:

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?

[JuggaloBrotha]

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:

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.