SQL Group_concat not get all data

Question

I have 2 table and second table use relationship

table1
id   name
---------
1    alpha
2    beta

table2
id   name  relation
-------------------
1    2015    2
2    2016    2
3    2017    2
4    2018    2

I want to see

name   data
-------------------------
beta   2015,2016,2017,2018
alpha  NULL

I tried the following sql query but the output is not what I wanted

I use:

SELECT 
    t1.name,
    GROUP_CONCAT(t2.name SEPARATOR ',')
FROM table1 AS t1
LEFT JOIN table2 AS t2
    ON t2.relation = t1.id

Output:

alpha 2015,2016,2017,2018

Alpha doesn't get any value in the other related tablature. the values in the output belong to the beta.

score 4 · Accepted Answer · answered Nov 30 '17 at 11:47

You need GROUP BY:

SELECT t1.name,
       GROUP_CONCAT(t2.name SEPARATOR ',')
FROM table1 t1 LEFT JOIN
     table2 t2
     ON t2.relation = t1.id
GROUP BY t1.name;

In most databases (and recent versions of MySQL), your query would fail. It is an aggregation query (because of the GROUP_CONCAT()). But, t1.name is not an argument to an aggregation function and it is not a GROUP BY key.

MySQL does allow this type of query. It returns exactly one row. The value of t1.name on the one row in the result set comes from an arbitrary row.

score 0 · Answer 2 · answered Nov 30 '17 at 11:47

No FKs for fiddle:

CREATE TABLE Table1    (`id` int, `name` varchar(5)) ;

INSERT INTO Table1
    (`id`, `name`)
VALUES
    (1, 'alpha'),
    (2, 'beta')
;


CREATE TABLE Table2    (`id` int, `name` int, `relation` int);

INSERT INTO Table2
    (`id`, `name`, `relation`)
VALUES
    (1, 2015, 2),
    (2, 2016, 2),
    (3, 2017, 2),
    (4, 2018, 2)
;

Statement:

SELECT 
    t1.name,
    GROUP_CONCAT(t2.name SEPARATOR ',') -- missing an AS .... => ugly name from fiddle
FROM table1 AS t1
LEFT JOIN table2 AS t2
    ON t2.relation = t1.id
group by t1.name

Output:

name    GROUP_CONCAT(t2.name SEPARATOR ',')
alpha   (null)
beta    2017,2018,2015,2016

SQL Group_concat not get all data

2 Answers2