From: Morten (listskikobu.com)
Date: Sun Oct 28 2007 - 09:46:47 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi,

I'm trying to write a query which returns a single record which contains
concatenated values for referencing records:

SELECT tickets.id AS id,
        CAST(GROUP_CONCAT(tags.name SEPARATOR ' ') AS CHAR) AS tags,
        CAST(GROUP_CONCAT(events.value SEPARATOR ' ') AS CHAR) AS text
FROM tickets
LEFT OUTER JOIN events AS events ON tickets.id = events.ticket_id
LEFT OUTER JOIN taggings AS taggings ON taggings.taggable_id = tickets.id
LEFT OUTER JOIN tags AS tags ON taggings.tag_id = tags.id
GROUP BY id;

The problem with this query is, that it returns too many matches in the
concatenated fields when more than one concatenation is used. Ie. given
the data:

CREATE TABLE tickets (id integer);
CREATE TABLE events (ticket_id integer, value varchar(32));
CREATE TABLE tags (id integer, name varchar(32));
CREATE TABLE taggings (taggable_id integer, tag_id integer);

INSERT INTO tickets VALUES (1);
INSERT INTO events VALUES (1, 'Event A');
INSERT INTO events VALUES (1, 'Event B');
INSERT INTO events VALUES (1, 'Event C');
INSERT INTO tags VALUES (1, 'Tag A');
INSERT INTO tags VALUES (2, 'Tag B');

INSERT INTO taggings VALUES (1, 1);
INSERT INTO taggings VALUES (1, 2);

The query returns duplicates:

+------+-------------------------------------+-------------------------------------------------+
| id | tags | text
                        |
+------+-------------------------------------+-------------------------------------------------+
| 1 | Tag A Tag B Tag A Tag B Tag A Tag B | Event A Event A Event B
Event B Event C Event C |
+------+-------------------------------------+-------------------------------------------------+

I suspect this has to do with the multiple GROUP_CONCATs as it works
fine when using only a single GROUP_CONCAT:

mysql> SELECT tickets.id AS id,
     -> CAST(GROUP_CONCAT(events.value SEPARATOR ' ') AS CHAR) AS
text
     -> FROM tickets
     -> LEFT OUTER JOIN events AS events ON tickets.id = events.ticket_id
     -> GROUP BY id
     -> ;
+------+-------------------------+
| id | text |
+------+-------------------------+
| 1 | Event A Event B Event C |
+------+-------------------------+

I'm tempted to solve this using a view or two, but would like to know if
there's a better way.

Br,

Morten

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]