how to make group_concat work in MySQL 5.7

Question

I have following tables and I want to use group_concat function in MySQL 5.7 but its showing error. Can anyone help me?

     **ride**                                         **route**
  r_id       Date1                             region  route_name  ri_id 
    1      2019-09-11                            DXB      dx1       1
    2      2019-09-11                            AUH      au1       2
    3      2019-09-11                            DXB      dx2       3
    4      2019-09-11                            NE       ne1       4

I am using the following query to group_concat but, it shows error like this

mysql Error Code: 3 error writing file 'C:\Winows\temp\Myffd3.tmp' (Errcode: 28).

SELECT region,GROUP_CONCAT(route_name) AS route_name FROM route LEFT JOIN ride ON ri_id = r_id WHERE  date1 = '2019-09-11' GROUP BY region

I want output like this

region    route_name
 DXB       dx1,dx2
 AUH       au1
 NE        ne1

If you are getting an error, you should always share the exact error message in your question. How are we supposed to guess? You will get better answers if you share this relevant information. — Bill Karwin, Nov 19 '19 at 06:29
this is the error mysql Error Code: 3 error writing file 'C:\Winows\temp\Myffd3.tmp' (Errcode: 28). — Rubin Anbin, Nov 19 '19 at 06:43
See this: https://stackoverflow.com/questions/7415710/mysql-writing-file-error-errcode-28 — Bill Karwin, Nov 19 '19 at 08:35

score 0 · Answer 1 · edited Nov 19 '19 at 06:20

0

The ON clause of your join has a syntax problem. You were right to use aliases in the query, but here is a version which might work:

SELECT
    ro.region,
    GROUP_CONCAT(ri.route_name) AS route_name
FROM route ro
LEFT JOIN ride ri
    ON ri.r_id = ro.r_id
WHERE
    ri.date1 = '2019-09-11'
GROUP BY
    ro.region;

edited Nov 19 '19 at 06:20

Rubin Anbin

188
2
13

answered Nov 19 '19 at 05:52

Tim Biegeleisen

502,043
27
286
360

score 0 · Answer 2 · answered Nov 19 '19 at 06:01

first, issue is you don't have alias on your table, so you ON operator will get confused which ID

second, use inner join so you will not have additional comma on results. (in addition to Tim Biegeleisen's answer)

select t1.region
    , group_concat(t1.route_name) AS route_name 
from route t1 
inner join ride t2 on t2.r_id = t1.r_id 
where  t2.date1 = '2019-11-05' 
group by t1.region

how to make group_concat work in MySQL 5.7

2 Answers2