MySQL - GROUP_CONCAT returns duplicate data, can't use DISTINCT

Question

I have a normalized database and I'm trying to return data from multiple tables using JOINs and GROUP_CONCAT.

Problem: Rows are being duplicated with GROUP_CONCAT. I can't use DISTINCT because some of the data (ingredient mfr) does need to be duplicated.

Here is my current query and db structure (SQL Fiddle):

SELECT recipe.*, 
GROUP_CONCAT(recipe_detail.ingredient_id) AS iid,  
GROUP_CONCAT(ingredient.name) AS iname, 
GROUP_CONCAT(ingredient_mfr.abbr) AS mabbr, 
GROUP_CONCAT(recipe_tag.name) AS tag
FROM  recipe
LEFT 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
LEFT 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.id

recipe
+------------+------------+-----------+
|    id      |    name    |  user_id  |
+============+============+===========+
|     1      |  Test123   |     1     |
+------------+------------+-----------+
|     2      |  Test456   |     1     |
+------------+------------+-----------+
|     3      |  Test789   |     1     |
+------------+------------+-----------+

recipe_detail
+------------+---------------+
| recipe_id  | ingredient_id |
+============+===============+
|     1      |      193      |
+------------+---------------+
|     1      |      194      |
+------------+---------------+
|     2      |       16      |
+------------+---------------+
|     3      |      277      |
+------------+---------------+

ingredient
+------------+---------------+---------+
|     id     |      name     |  mfr_id |
+============+===============+=========+
|     16     |       Gin     |    4    |
+------------+---------------+---------+
|     193    |       Fig     |    3    |
+------------+---------------+---------+
|     194    |       Tea     |    3    |
+------------+---------------+---------+
|     277    |       Nut     |    2    |
+------------+---------------+---------+

ingredient_mfr
+------------+------------+
|    id      |    abbr    |
+============+============+
|     2      |    TFA     |
+------------+------------+
|     3      |    FA      |
+------------+------------+
|     4      |    LOR     |
+------------+------------+

recipe_tag
+------------+------------+
|    id      |    name    |
+============+============+
|     1      |    one     |
+------------+------------+
|     2      |    two     |
+------------+------------+
|     3      |    three   |
+------------+------------+
|     4      |    four    |
+------------+------------+
|     5      |    five    |
+------------+------------+
|     6      |    six     |
+------------+------------+
|     7      |    seven   |
+------------+------------+
|     8      |    eight   |
+------------+------------+
|     9      |    nine    |
+------------+------------+

recipe_tagmap
+------------+---------------+---------+
|     id     |   recipe_id   |  tag_id |
+============+===============+=========+
|     1      |       1       |    1    |
+------------+---------------+---------+
|     2      |       1       |    2    |
+------------+---------------+---------+
|     3      |       1       |    3    |
+------------+---------------+---------+
|     4      |       2       |    4    |
+------------+---------------+---------+
|     5      |       2       |    5    |
+------------+---------------+---------+
|     6      |       2       |    6    |
+------------+---------------+---------+
|     7      |       3       |    7    |
+------------+---------------+---------+
|     8      |       3       |    8    |
+------------+---------------+---------+
|     9      |       3       |    9    |
+------------+---------------+---------+

With my current query, my results look like this:

+------+---------+--------------+----------- ----+---------------+------------------+
|  id  |  name   |      iid     |     iname      |    mabbr      |       tag        |
+======+=========+==============+================+===============+==================+
|   1  | Test123 | 193,193,193, | Fig, Fig, Fig, | FA, FA, FA,   | one, two, three, |
|      |         | 194,194,194  | Tea, Tea, Tea  | FA, FA, FA    | one, two, three  |
+------+---------+--------------+----------------+---------------+------------------+
|   2  | Test456 | 16,16,16     | Gin, Gin, Gin  | LOR, LOR, LOR | four, five six   |
+------+---------+--------------+----------------+---------------+------------------+
|   3  | Test789 | 277,277,277  | Nut, Nut, Nut  | TFA, TFA, TFA | seven,eight,nine |
+------+---------+--------------+----------------+---------------+------------------+

What I would like my results to look like:

+------+---------+--------------+----------- ----+---------------+------------------+
|  id  |  name   |      iid     |     iname      |    mabbr      |       tag        |
+======+=========+==============+================+===============+==================+
|   1  | Test123 |   193, 194   |    Fig, Tea    |    FA, FA     | one, two, three, |
+------+---------+--------------+----------------+---------------+------------------+
|   2  | Test456 |      16      |      Gin       |     LOR       | four, five six   |
+------+---------+--------------+----------------+---------------+------------------+
|   3  | Test789 |     277      |      Nut       |     TFA       | seven,eight,nine |
+------+---------+--------------+----------------+---------------+------------------+

As you can see, the presence of multiple tags causes the ingredient data to duplicate. The presence of multiple ingredients causes the tags to duplicate. I have tried to use DISTINCT, but sometimes I will have multiple ingredients and each one of those will return it's own "mabbr", which may be the same it's other ingredient (see first row of expected results). Using DISTINCT, it will only return one instance of that "mabbr".

Is there a change I can make to my query to achieve what I'd like to do?

SQL Fiddle

+1 for a nicely asked question, showing your starting data, what you've tried (er, well, in the future pull that in here too please), the problem you're facing (and that you know what's causing it!), and what you want the results to be. #sigh# I wish more new users would ask questions that looked like this... — Clockwork-Muse, May 04 '14 at 13:48
Thank you for the compliment. I know it's easier for you guys to answer if you have as much information as possible. — Matt Shultz, May 04 '14 at 17:25

transilvlad · Accepted Answer · 2014-05-04T15:53:49.787

6

You can resolve this by extracting the tag grouping to its own subquery:

SELECT
    recipe.*,
    GROUP_CONCAT(recipe_detail.ingredient_id) AS iid,
    GROUP_CONCAT(ingredient.name) AS iname,
    GROUP_CONCAT(ingredient_mfr.abbr) AS mabbr,
    (
      SELECT GROUP_CONCAT(recipe_tag.name)
        FROM recipe_tag
          INNER JOIN recipe_tagmap
            ON recipe_tagmap.tag_id = recipe_tag.id
        WHERE recipe_tagmap.recipe_id = recipe.id
     ) AS tag

  FROM recipe
    LEFT 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.id

(example fiddle)

edited May 04 '14 at 15:53

answered May 04 '14 at 03:40

transilvlad

13,974
13
45
80

In your example, mabbr is still being duplicated more times than it should. – Matt Shultz May 04 '14 at 03:56
1

+1 for getting a working answer. I've pulled it in from the link for you, because otherwise it should have been removed, especially as it did **NOT** match what your answer said. I've updated the `JOIN` used in the subquery to match what the optimizer is actually giving you. Note that for large result sets it may be more efficient to do (a modified version of) the `tag2` subquery as a table-reference, instead of being in the `SELECT` list. – Clockwork-Muse May 04 '14 at 13:44

score -1 · Answer 2 · answered Apr 17 '19 at 11:18

Adding distinct while doing GROUP_CONCAT will give you unique values.

SELECT recipe.*, 
GROUP_CONCAT(distinct recipe_detail.ingredient_id) AS iid,  
GROUP_CONCAT(distinct ingredient.name) AS iname, 
GROUP_CONCAT(distinct ingredient_mfr.abbr) AS mabbr, 
GROUP_CONCAT(distinct recipe_tag.name) AS tag
FROM  recipe
LEFT 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
LEFT 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.id

SQL Fiddle

as OP said he can't use DISTINCT because some values have to be duplicated — Andrea Mauro, May 20 '20 at 16:47

MySQL - GROUP_CONCAT returns duplicate data, can't use DISTINCT

2 Answers2

Related