58

I'm experiencing a problem with MySQL's "GROUP_CONCAT" function. I will illustrate my problem using a simple help desk database:

CREATE TABLE Tickets (
 id INTEGER NOT NULL PRIMARY KEY,
 requester_name VARCHAR(255) NOT NULL,
 description TEXT NOT NULL);

CREATE TABLE Solutions (
 id INTEGER NOT NULL PRIMARY KEY,
 ticket_id INTEGER NOT NULL,
 technician_name VARCHAR(255) NOT NULL,
 solution TEXT NOT NULL,
 FOREIGN KEY (ticket_id) REFERENCES Tickets.id);

INSERT INTO Tickets VALUES(1, 'John Doe', 'My computer is not booting.');
INSERT INTO Tickets VALUES(2, 'Jane Doe', 'My browser keeps crashing.');
INSERT INTO Solutions VALUES(1, 1, 'Technician A', 'I tried to solve this but was unable to. I will pass this on to Technician B since he is more experienced than I am.');
INSERT INTO Solutions VALUES(2, 1, 'Technician B', 'I reseated the RAM and that fixed the problem.');
INSERT INTO Solutions VALUES(3, 2, 'Technician A', 'I was unable to figure this out. I will again pass this on to Technician B.');
INSERT INTO Solutions VALUES(4, 2, 'Technician B', 'I re-installed the browser and that fixed the problem.');

Notice that this help desk database has two tickets, each with two solution entries. My goal is to use a SELECT statement to create a list of all of the tickets in the database with their corrosponding solution entries. This is the SELECT statement I'm using:

SELECT Tickets.*, GROUP_CONCAT(Solutions.solution) AS CombinedSolutions
FROM Tickets
LEFT JOIN Solutions ON Tickets.id = Solutions.ticket_id
ORDER BY Tickets.id;

The problem with the above SELECT statement is it's returning only one row:

id: 1
requester_name: John Doe
description: My computer is not booting.
CombinedSolutions: I tried to solve this but was unable to. I will pass this on to Technician B since he is more experienced than I am.,I reseated the RAM and that fixed the problem.,I was unable to figure this out. I will again pass this on to Technician B.,I re-installed the browser and that fixed the problem.

Notice that it's returning ticket 1's information with both ticket 1's and ticket 2's solution entries.

What am I doing wrong? Thanks!

Nick
  • 591
  • 1
  • 4
  • 5

3 Answers3

107

Use:

   SELECT t.*,
          x.combinedsolutions
     FROM TICKETS t
LEFT JOIN (SELECT s.ticket_id,
                  GROUP_CONCAT(s.soution) AS combinedsolutions
             FROM SOLUTIONS s 
         GROUP BY s.ticket_id) x ON x.ticket_id = t.ticket_id

Alternate:

   SELECT t.*,
          (SELECT GROUP_CONCAT(s.soution)
             FROM SOLUTIONS s 
            WHERE s.ticket_id = t.ticket_id) AS combinedsolutions
     FROM TICKETS t
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 5
    But why doesn't Nick's method work? It obviously doesn't, but it seems like it should. Can you explain it to me? – pbarney Oct 29 '13 at 14:46
  • So far I understand it group_contact doesn't care about the parts sof the query, it groups all the intermediate results. – jnovacho Dec 22 '13 at 11:32
  • 14
    I thought my `join`s were wrong but I was just missing a `GROUP BY` statement so `GROUP_CONCAT()` didn't aggregate values. Hope this saves someone time, like the hour I spent going cross eyed. – Dylan Valade Mar 16 '14 at 20:08
  • 2
    The 'alternate' answer given here is **much** more performant for larger data sets – Andy Lorenz Nov 14 '14 at 11:31
  • 1
    The alternate answer looks far cleaner. That's the one for me. And it works! – Alexander Flenniken Jan 22 '17 at 09:05
  • The first solution is not work for me, it returns NULL. The alternate one will not work if you need to group_concat more than one table, for example SELECT GROUP_CONCAT(s.soution1), GROUP_CONCAT(s.soution2), – Luvias Mar 06 '18 at 12:51
  • I would like to point on the GROUP_CONCAT can have any separator you like. Eg, separated by comma WITH a space: `SELECT GROUP_CONCAT(s.soution separator ', ')` – Melroy van den Berg Jan 07 '19 at 22:33
8

You just need to add a GROUP_BY :

SELECT Tickets.*, GROUP_CONCAT(Solutions.solution) AS CombinedSolutions FROM Tickets 
LEFT JOIN Solutions ON Tickets.id = Solutions.ticket_id 
GROUP_BY Tickets.id 
ORDER BY Tickets.id;
Frederic WOEHL
  • 81
  • 1
  • 2
  • 1
    This was the case for me. The accepted answer also worked but made my query significantly harder to read. Adding `GROUP_BY ...` gave me the behavior I wanted. – Mark Feb 11 '22 at 17:24
3

I think @Dylan Valade's comment is the simplest answer so I'm posting it as another answer: simply adding a GROUP BY Tickets.id to the OP's SELECT should fix the issue. It fixed my own issue.

However, for databases that are not small the accepted answer, especially if there are any predicates on Tickets.id appears to not involve a total table scan and so while the previous paragraph returns the correct results it appears to be much less efficient in my case.

ghr
  • 505
  • 4
  • 8