CODEWITHSUNDEEP
mysqlsqlconcatenationgroup-concat
hberbary
hberbary

Reputation: 157

Using a CONCAT with GROUP_CONCAT

I have to export some data to a excel file. This data is from a table named "tb_Forms" and has a multiple relation in another table named "tb_Movement", as shown below

+----------+---------------------+
| tb_Forms |                     |
+----------+---------------------+
| FormsId  | Description         |
| 1        | "form 1"            |
| 2        | "form 2"            |
| 3        | "form 3"            |
+----------+---------------------+  

+-----------------+---------------+-------------------+--------------+--------+-----------------------+
|   tb_Movement   |               |                   |              |        |                       |
+-----------------+---------------+-------------------+--------------+--------+-----------------------+
| MovementId      | FirstAnswer   | SecondAnswer      | ResponseDate | UserFk | FormsFk               |
| 1               | "Lorem Ipsum" | "dolor sit amet"  | 2018-07-31   | 1      | 1                     |
| 2               | "consectetur" | "adipiscing elit" | 2018-08-01   | 2      | 1                     |
+-----------------+---------------+-------------------+--------------+--------+-----------------------+

+----------+---------------------+
| tb_Users |                     |
+----------+---------------------+
| UserId   | Name                |
| 1        | João Silva          |
| 2        | Maria Oliveira      |
+----------+---------------------+

I want to find a way to get two results like:

Result
First Remark: "Lorem Ipsum",
Second Remark: "dolor sit amet" in 2018-07-31 by João Silva
First Remark: "consectetur", 
Second Remark: "adipiscing elit" in 2018-08-01 by Maria Oliveira

But I can only get the first result (by João Silva) and nothing more. Here is the query I did:

SELECT GROUP_CONCAT(CONCAT(
    "First Remark: ", mv.FirstRemark,
    "\rSecond Remark: ", mv.SecondRemark,
    "\rin ", mv.ResponseDate, " by ",
    (SELECT Name FROM tb_Users WHERE UserId = mv.UserFk)
) SEPARATOR ',')
FROM tb_Movement mv 
WHERE mv.FormsFk = 1;

I already try:

SET SESSION group_concat_max_len = 1000000;

..because the remarks field could be very large, but this not appear to be the solution.

I have to concat all answers like this because each Form row also has to be a unique row in excel.

Upvotes: 1

Views: 238

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I would recommend:

SELECT GROUP_CONCAT('First Remark: ', mv.FirstRemark,
                    '\rSecond Remark: ', mv.SecondRemark,
                    '\rin ', mv.ResponseDate, 
                    ' by ', u.name
                    SEPARATOR ','
                   )
FROM tb_Movement mv LEFT JOIN
     tb_Users u
     ON u.userId = mv.UserFK
WHERE mv.FormsFk = 1;

Notes:

  • The standard string delimiter in SQL is the single quote.
  • GROUP_CONCAT() handles multiple arguments, so CONCAT() is not necessary.
  • You need to use a JOIN. Using a correlated subquery in an aggregation query either produces unexpected results or an error -- neither is desirable.
  • A LEFT JOIN is closer to your intention, so NULL foreign key references are not filtered out.

Upvotes: 3

Related Questions