MySQL group concat not showing distinct values when joined to another table

All we need is an easy explanation of the problem, so here it is.

I have one view that gives me comma separated values and want to use that data in another view. In the first view, I am using group_concat( distinct …) and when I run that view by itself, I can see only the distinct values as expected.

However, when I join that view to another table, I am now getting duplicates.

SELECT
group_concat( distinct tag separator ', ') AS tag,
FROM
fruit

tag is already group_concated in the first view. Running this query gives me:

apples, oranges, apples, pears, figs, oranges

Apples and oranges are duplicated. Why is this?

EDIT:

I’ve paired this down as much as I could. Here is the view with no joins.

SELECT
tag
FROM
fruit

Here are the rows returned:

apples, oranges
apples, pears
figs, oranges

How do I get a group_concat for the column with an output of:

apples, oranges, pears, figs

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I entered that data like this

mysql> use test
Database changed
mysql> drop table if exists fruit;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table fruit
    -> (
    ->   id int not null auto_increment primary key,
    ->   tag varchar(20) not null
    -> );
Query OK, 0 rows affected (0.45 sec)

mysql> insert into fruit (tag) values
    -> ('apples'),('oranges'),('apples'),('pears'),('figs'),('oranges');
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

Note the output of these queries

mysql> select tag from fruit;
+---------+
| tag     |
+---------+
| apples  |
| oranges |
| apples  |
| pears   |
| figs    |
| oranges |
+---------+
6 rows in set (0.03 sec)

mysql> select distinct tag from fruit;
+---------+
| tag     |
+---------+
| apples  |
| oranges |
| pears   |
| figs    |
+---------+
4 rows in set (0.00 sec)

mysql> select group_concat(distinct tag) tag from fruit;
+---------------------------+
| tag                       |
+---------------------------+
| apples,oranges,pears,figs |
+---------------------------+
1 row in set (0.00 sec)

mysql>

According the info you just added to the question, each tag has two fruits. Please note that each tag field (holding two fruits) is unique. You should be storing the fruits as separate fields.

The GROUP_CONCAT function is for query aggregation, not set manipulation.

The only way to use the view is to do this convoluted solution

I reloaded the data like this first:

mysql> use test
Database changed
mysql> drop table if exists fruit;
Query OK, 0 rows affected (0.12 sec)

mysql> create table fruit
    -> (
    ->   id int not null auto_increment primary key,
    ->   tag varchar(20) not null
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> insert into fruit (tag) values
    -> ('apples, oranges'),('apples, pears'),('figs, oranges');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

Here are the contents now

mysql> select tag from fruit;
+-----------------+
| tag             |
+-----------------+
| apples, oranges |
| apples, pears   |
| figs, oranges   |
+-----------------+
3 rows in set (0.00 sec)

mysql> select distinct tag from fruit;
+-----------------+
| tag             |
+-----------------+
| apples, oranges |
| apples, pears   |
| figs, oranges   |
+-----------------+
3 rows in set (0.00 sec)

mysql>

Ready for the convoluted solution ???

Here is the code

SELECT CONCAT('SELECT GROUP_CONCAT(subtag) fruits FROM (SELECT ''',
REPLACE(tags,', ',''' subtag UNION SELECT '''),''') A') INTO @TagSQL
FROM (select GROUP_CONCAT(tag separator ', ') tags FROM fruit) A;
SELECT @TagSQL\G
PREPARE s FROM @TagSQL; EXECUTE s; DEALLOCATE PREPARE s;

Here is the output

mysql> SELECT CONCAT('SELECT GROUP_CONCAT(subtag) fruits FROM (SELECT ''',
    -> REPLACE(tags,', ',''' subtag UNION SELECT '''),''') A') INTO @TagSQL
    -> FROM (select GROUP_CONCAT(tag separator ', ') tags FROM fruit) A;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @TagSQL\G
*************************** 1. row ***************************
@TagSQL: SELECT GROUP_CONCAT(subtag) fruits FROM (SELECT 'apples' subtag UNION S
ELECT 'oranges' subtag UNION SELECT 'apples' subtag UNION SELECT 'pears' subtag
UNION SELECT 'figs' subtag UNION SELECT 'oranges') A
1 row in set (0.00 sec)

Now, execute the SQL generated

mysql> PREPARE s FROM @TagSQL; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+---------------------------+
| fruits                    |
+---------------------------+
| apples,oranges,pears,figs |
+---------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

I TOLD YOU IT WAS CONVOLUTED !!!

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply