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