Add a JOIN to GROUP_CONCAT()

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?

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;

StackExchange Database Administrators Q#159765, answer score: 7