Using a CONCAT with GROUP_CONCAT

Question

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.

Gordon Linoff · Accepted Answer · 2018-08-01 20:47:27Z

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.

Collectives™ on Stack Overflow

Using a CONCAT with GROUP_CONCAT

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related