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

Hi,

Morten wrote:
>
> 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.

GROUP_CONCAT() takes an optional DISTINCT modifier, and that might do
what you're looking for.

Baron

Baron Schwartz wrote:

>> I'm tempted to solve this using a view or two, but would like to know
>> if there's a better way.
>
> GROUP_CONCAT() takes an optional DISTINCT modifier, and that might do
> what you're looking for.
>

It sure does the trick. I'll use that, I was afraid that I was missing
something fundamental in the joins.

Thanks,

Morten

Martin Gainty wrote:
> Good Morning-

Good afternoon :-)

> http://www.mysqlperformanceblog.com/...eful-group-by-
> extension/
> I did'nt see your where clause ?

I'm probably missing your point here. But there's no "where clause"
because I want all records from the tickets table returned, and I do the
joins using outer left joins.

Br,

Morten