MySQL – Adding JOIN to GROUP_CONCAT()

group-concatenationjoin;MySQL

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?

Best Answer

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;

Related Solutions

Mysql – Join two group_concat result

You can reverse the order of the two functions, so the concatenation of group_name and id is done first - using either CONCAT() or CONCAT_WS() - and then the group concatenation:

GROUP_CONCAT( CONCAT_WS(':', rtg.group_name, rtg.id) 
              ORDER BY rtg.id
              SEPARATOR ','                      -- the comma ',' is the default
            ) AS groupList

MySQL GROUP_CONCAT – Fix Duplicate Data Issue When DISTINCT Can’t Be Used

You have identified the source of the problem: that recipe is joined to two tables, recipe_detail and recipe_tagmap (and these to several other tables related to respectively "ingredients" and "tags"), and recipe is having one-to-many relationships with both of them.

One solution is to individually GROUP BY and aggregate first (one aggregation for the list of the tables related to ingredients and another for the group of tables related to tags, and then join back (again) to the main table (recipe):

SELECT recipe.*, 
       iid,  
       iname, 
       mabbr, 
       tag
FROM  recipe

  LEFT JOIN 
    ( SELECT recipe_detail.recipe_id,
             GROUP_CONCAT(recipe_detail.ingredient_id) AS iid,  
             GROUP_CONCAT(ingredient.name) AS iname, 
             GROUP_CONCAT(ingredient_mfr.abbr) AS mabbr
      FROM recipe
        JOIN recipe_detail
          ON recipe.id = recipe_detail.recipe_id
        LEFT JOIN ingredient
          ON recipe_detail.ingredient_id = ingredient.id
        LEFT JOIN ingredient_mfr
          ON ingredient.mfr_id = ingredient_mfr.id
      WHERE recipe.user_id = 1
      GROUP BY recipe_detail.recipe_id
    ) AS details
        ON recipe.id = details.recipe_id

  LEFT JOIN
    ( SELECT recipe_tagmap.recipe_id,
             GROUP_CONCAT(recipe_tag.name) AS tag 
      FROM recipe
        JOIN recipe_tagmap
          ON recipe.id = recipe_tagmap.recipe_id
        LEFT JOIN recipe_tag
         ON recipe_tagmap.tag_id = recipe_tag.id
      WHERE recipe.user_id = 1
      GROUP BY recipe_tagmap.recipe_id
    ) AS tags
      ON recipe.id = tags.recipe_id

WHERE recipe.user_id = 1 ;

Tested at: SQL-Fiddle

(Using the recipe table inside the 2 aggregations is not strictly needed but since you only want the recipes of one user, it will help for efficiency, restricting the number of rows retrieved from several tables and aggregated.)

Best Answer

Related Solutions

Mysql – Join two group_concat result

MySQL GROUP_CONCAT – Fix Duplicate Data Issue When DISTINCT Can’t Be Used

Related Question