Conditions in MySQL GROUP_CONCAT

Question

I have a MySQL table with requests

+--------+-------------+-----+
| req_id | req_name    | ... |
+--------+-------------+-----+
| 1      | testrequest | ... |
+--------+-------------+-----+

and a table with votes on such requests.

+--------+-----------+----------+
| req_id | vote_name | approved |
+--------+-----------+----------+
| 1      | User1     | 1        |
| 1      | User2     | 1        |
| 1      | User3     | 1        |
| 1      | User4     | 0        |
| 1      | User5     | 0        |
+--------+-----------+----------+

The kind of view I want:

+--------+-------------+---------------------+--------------+
| req_id | req_name    | approved_by         | rejected_by  |
+--------+-------------+---------------------+--------------+
| 1      | testrequest | User1, User2, User3 | User4, User5 |
+--------+-------------+---------------------+--------------+

So far, however, I've only been able to accomplish this:

+--------+-------------+----------+---------------------+
| req_id | req_name    | approved | by                  |
+--------+-------------+----------+---------------------+
| 1      | testrequest | YES      | User1, User2, User3 |
| 1      | testrequest | NO       | User4, User5        |
+--------+-------------+----------+---------------------+

The query I used:

SELECT requests.req_id, req_name, CASE
        WHEN approved THEN 'YES'
        ELSE 'NO'
        END AS approved, GROUP_CONCAT(vote_name ORDER BY vote_name ASC SEPARATOR ', ') AS by
FROM requests
LEFT JOIN votes ON requests.req_id = votes.req_id
GROUP BY requests.req_id, approved
ORDER BY requests.req_id DESC;

So my question is, how do I get 2 group_concats in the same row with different values?

Thanks a lot!

`Order by` is working with `group_concat` – safarov Mar 23 '12 at 17:38 — safarov, Mar 23 '12 at 17:38

score 41 · Accepted Answer · answered Mar 23 '12 at 18:04

41

Try this:

select r.req_id, r.req_name,
  group_concat(if(approved, vote_name, null) separator ', ') approvedBy,
  group_concat(if(approved, null, vote_name) separator ', ') rejectedBy
from requests r
left join votes v on r.req_id = v.req_id

Result:

+--------+-------------+---------------------+--------------+
| REQ_ID |  REQ_NAME   |     APPROVEDBY      |  REJECTEDBY  |
+--------+-------------+---------------------+--------------+
|      1 | testrequest | User1, User2, User3 | User4, User5 |
+--------+-------------+---------------------+--------------+

answered Mar 23 '12 at 18:04

Mosty Mostacho

42,742
16
96
123

Hey man. How would I select just one of the vote_names given that multiple rows could have `approved` set to true or false. I have a strange situation where I get multiple rows concantinated into one because they have the `approved` field set to the same value. In this case, I'd like to only select one row. I have one other column that's distinct but not using it in my statement. When I use it, MySQL complains it's not part of the group_by – TheRealChx101 Sep 12 '22 at 00:09

score 1 · Answer 2 · answered Mar 23 '12 at 17:59

1

I tried re-using your query inside another query

SELECT req_id,
   req_name,
   GROUP_CONCAT(case approved when 'YES' then voted_by else null end SEPARATOR ', ') AS approved_by,
   GROUP_CONCAT(case approved when 'NO' then voted_by else null end SEPARATOR ', ') AS rejected_by
FROM
(
SELECT requests.req_id, req_name, CASE
    WHEN approved THEN 'YES'
    ELSE 'NO'
    END AS approved, 
    GROUP_CONCAT(vote_name ORDER BY vote_name ASC SEPARATOR ', ') AS voted_by
FROM requests
LEFT JOIN votes ON requests.req_id = votes.req_id
GROUP BY requests.req_id, approved
ORDER BY requests.req_id DESC
) t
group by req_id

answered Mar 23 '12 at 17:59

Chetter Hummin

6,687
8
32
44

Thanks for the quick reply! Do you think it's possible to achieve this without using a subquery? Because if I'm not mistaken, MySQL does not allow subqueries in the FROM-clause of a view.. – Rapsey Mar 23 '12 at 18:04
Ah ok. Will have to look into it. – Chetter Hummin Mar 23 '12 at 18:13

Conditions in MySQL GROUP_CONCAT

2 Answers2