mysql left join group by,MySQL：带有LEFT JOIN的GROUP_CONCAT

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!

解决方案

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