#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    1
    Rep Power
    0

    An issue with group_concat selecting multiple rows.


    I have data in a table that looks like so:

    Code:
    +----+-------------+------------+----------+
    | id | name        | date       | status   |
    +----+-------------+------------+----------+
    |  1 | James Grew  | 2013-03-12 | Approved |
    |  2 | James Dew   | 2013-03-12 | Approved |
    |  3 | James Moo   | 2013-03-12 | Approved |
    |  4 | James Beard | 2013-03-12 | Pending  |
    |  5 | James Beer  | 2013-03-12 | Pending  |
    |  6 | James Sneer | 2013-03-12 | Pending  |
    |  7 | James Fear  | 2013-03-12 | Pending  |
    +----+-------------+------------+----------+
    What I am trying to accomplish is a query that will do the following: Select * from the table where status is approved, AND if there are any rows with a status of Pending whose date matches the approved, add those to the end. Here's my query and what it returns:

    Code:
    mysql> select *, group_concat( (case when status = 'Pending' then name end) separator ';') as Pending from requests group by `date`;
    +----+------------+------------+----------+-----------------------------------------------+
    | id | name       | date       | status   | Pending                                       |
    +----+------------+------------+----------+-----------------------------------------------+
    |  1 | James Grew | 2013-03-12 | Approved | James Beard;James Beer;James Sneer;James Fear |
    +----+------------+------------+----------+-----------------------------------------------+
    The problem? It doesn't return all approved... I'm trying to get this:

    Code:
    +----+------------+------------+----------+-----------------------------------------------+
    | id | name       | date       | status   | Pending                                       |
    +----+------------+------------+----------+-----------------------------------------------+
    |  1 | James Grew | 2013-03-12 | Approved | James Beard;James Beer;James Sneer;James Fear |
    |  2 | James Dew  | 2013-03-12 | Approved | James Beard;James Beer;James Sneer;James Fear |
    |  3 | James Moo  | 2013-03-12 | Approved | James Beard;James Beer;James Sneer;James Fear |
    +----+------------+------------+----------+-----------------------------------------------+
    In other words, return ALL approved as their own separate row AND if there are rows with a status of Pending whose date matches that of the approved, add those as a semicolon delmited column at the end (if no pending, then column is empty).

    Any help in how to accomplish this is greatly appreciated.

    Thank You!
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,117
    Rep Power
    9398
    Try with a JOIN. On the left is the table with the status=Approved rows, on the right is the table again but with the status=Pending rows with the same date.

IMN logo majestic logo threadwatch logo seochat tools logo