CODEWITHSUNDEEP
mysqlsqlconcatenationgroup-concat
Dario
Dario

Reputation: 640

SQL GROUP_CONCAT not working with 2 table join

I have this query which is working as expected:

Query

SELECT name, date, enddate, abo_name, bill_id
FROM (
    SELECT CONCAT(c.firstname, " ", c.surname) AS name, GROUP_CONCAT(b.date) AS date, GROUP_CONCAT(b.endDate) AS enddate, GROUP_CONCAT(b.abo_id) AS abo_name, GROUP_CONCAT(b.id) AS bill_id
    FROM customer c
    LEFT JOIN bill b ON c.id = b.customer_id
    GROUP BY name
) t

Output (JSON)

{
    name: "Max Mustermann",
    date: "2018-09-13,2018-09-27",
    enddate: "2018-09-13,2018-09-28",
    abo_name: "1,2",
    bill_id: "23,27"
},
...

But now I need to add more detailed information to the abo_name property, I need to output the full abo name. I tried to just add an INNER JOIN to the query, but then the GROUP_CONCAT doesn't work anymore.

Here's the example:

Query

SELECT name, date, enddate, abo_name, bill_id
FROM (
    SELECT CONCAT(c.firstname, " ", c.surname) AS name, GROUP_CONCAT(b.date) AS date, GROUP_CONCAT(b.endDate) AS enddate, GROUP_CONCAT(a.name) AS abo_name, GROUP_CONCAT(b.id) AS bill_id
    FROM customer c
    LEFT JOIN bill b ON c.id = b.customer_id
    INNER JOIN abo a ON b.abo_id = a.id
    GROUP BY name
) t

Output (JSON)

{
    name: "Max Mustermann",
    date: "2018-09-13",
    enddate: "2018-09-13",
    abo_name: "Einzelstunde",
    bill_id: "23"
},
{
    name: "Max Mustermann",
    date: "2018-09-27",
    enddate: "2018-09-28",
    abo_name: "Schnupperstunde",
    bill_id: "27"
},

As you can see the data doesn't concat and 2 objects are created. Does anybody have an idea why this is the case?

Thank you for your time and help!

Upvotes: 1

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

abo has a column named name. Aggregate by the expression or use a different alias:

SELECT CONCAT(c.firstname, ' ', c.surname) AS new_name, GROUP_CONCAT(b.date) AS date, GROUP_CONCAT(b.endDate) AS enddate, GROUP_CONCAT(a.name) AS abo_name, GROUP_CONCAT(b.id) AS bill_id
FROM customer c LEFT JOIN
     bill b
     ON c.id = b.customer_id INNER JOIN
     abo a
     ON b.abo_id = a.id
GROUP BY new_name

Upvotes: 1

Related Questions