0

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
  • Where is the JOIN query ??? – RolandoMySQLDBA Apr 22 '14 at 2:27
  • I apologize for the misleading title, If I run the query just as it is, without a join, it still gives me duplicates – James K Apr 22 '14 at 2:28
0

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 !!!

  • Maybe I'm missing select distinct on the query itself. Thank you Rolando! You went all out there for me and I appreciate it. I'll be back and let you know if that works. – James K Apr 22 '14 at 2:45
  • Rolando, the tags are coming from a single table and then concated in the query. The database is very well normalized. – James K Apr 22 '14 at 2:48
  • The tags in each row is seen by the GROUP_CONCAT as a single value. Thus, you are using GROUP_CONCAT to combine apples, oranges as the first value, apples, pears as the second value, and figs, oranges as the third value. Each pair is unique. – RolandoMySQLDBA Apr 22 '14 at 2:52
  • I see.. So as far as the group_concat function is concerned, it's doing its job then. What I need are distinct values for each row so if I forgo the fruit view and just join up the tables, I get what I need. I've already tested this but I want to be certain that there is no way to do it with the view first. Am I right? – James K Apr 22 '14 at 2:54
  • I think what I'm after here is a group_concat on the column, not the rows. Just to clarify. I actually saw this after I posted for help. – James K Apr 22 '14 at 2:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.