Add a JOIN to GROUP_CONCAT()

Question

I have a table of mostly-static objectives and another to track users' completions of those objectives. They can associate the completion with one or more records entered in another table and/or a text note. I'd like to format all of this together into a single entry for displaying in a table (i.e. one row per objective).

Here's an example of what Completion might look like:

ID    userID    objectiveID    recordID    text
1     4         8              500         NULL
2     4         8              NULL        "Lorem ipsum..."
3     4         8              750         NULL

I've gotten this far:

SELECT objectiveID,
   GROUP_CONCAT(recordID SEPARATOR ',') AS records,
   GROUP_CONCAT(text SEPARATOR ',') AS text
FROM Completion AS c
GROUP BY objectiveID;

Which returns:

objectiveID    records    text
8              "500,750"  "Lorem ipsum..."

What I'd actually like to display, however, is an attribute of the code being referenced by recordID... Suppose that this is the Record table:

ID    userID    codeID
500   4         1111
750   4         2222

And that this is the Code table:

ID    description
1111  dolor
2222  sit amet

My desired output would be:

objectiveID    records            text
8              "dolor, sit amet"  "Lorem ipsum..."

What's the best approach to get the other values merged in?

score 7 · Accepted Answer · answered Jan 02 '17 at 21:39

You join Completion (columns userID,recordID) to Record (columns userID,ID)

You join Record (column codeID) to Code (column ID)

Here is the proposed query

SELECT c.objectiveID,
   GROUP_CONCAT(d.description SEPARATOR ',') AS records,
   GROUP_CONCAT(c.text SEPARATOR ',') AS text
FROM Completion AS c
INNER JOIN Record AS r ON c.userID=r.userID AND c.recordID=r.ID
INNER JOIN Code   AS d ON r.codeID=d.ID
GROUP BY c.objectiveID;

Since GROUP_CONCAT's default separator is a comma, you could rewrite as

SELECT c.objectiveID,
   GROUP_CONCAT(d.description) AS records,
   GROUP_CONCAT(c.text) AS text
FROM Completion AS c
INNER JOIN Record AS r ON c.userID=r.userID AND c.recordID=r.ID
INNER JOIN Code   AS d ON r.codeID=d.ID
GROUP BY c.objectiveID;

Add a JOIN to GROUP_CONCAT()

1 Answers1