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
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
