Double GROUP_CONCAT with MySQL 5.6

Question

I've got a query that bugs my mind right now.

Here's my basic query :

SELECT e.id AS `equipment.id`, t.id AS `task.id`, ttr.tags_id AS `tag.id`
FROM equipment e
  LEFT JOIN task t ON t.equipment = e.id
  INNER JOIN task_tag_relationship ttr ON ttr.task_id = t.id
WHERE e.id = 14;

And its result:

equipment.id  task.id   tag.id
14            10520     171
14            152040    171
14            223738    1
14            223738    4
14            331035    1
14            331035    4
14            1475152   1
14            1475152   4
14            1475152   5

What I'd like is to concat the tags.ids from each task with, let's say a ,, and then to concat these concatenated tags.ids with ;:

equipment.id  tags.ids
14            171;171;1,4;1,4;1,4,5

I've managed to do it with the following query:

SELECT GROUP_CONCAT(tags SEPARATOR ';') as `tags.ids`
FROM (
    SELECT GROUP_CONCAT(ttr.tags_id) as `tags`
    FROM task t
      INNER JOIN task_tag_relationship ttr ON ttr.task_id = t.id
    WHERE t.equipment = 14
    GROUP BY t.id
) as `task.tags`;

But the thing is that I cannot use that query into a query like this:

SELECT
  e.id, 
  SELECT GROUP_CONCAT(tags SEPARATOR ';') as `tags.ids`
  FROM (
    SELECT GROUP_CONCAT(ttr.tags_id) as `tags`
    FROM task t
      INNER JOIN task_tag_relationship ttr ON ttr.task_id = t.id
    WHERE t.equipment = e.id -- here's the problem, e.id is not available in the sub-subquery
    GROUP BY t.id
  ) as `task.tags`
FROM equipment e
WHERE e.id = 14

I've been trying to do it with JOINs and GROUP BYs but no better results.

I could obviously do it in code but I'm wondering if that's doable in MySQL.

Thanks in advance!

user_521799 · Answer

You were close. Try this:

SELECT equipment, GROUP_CONCAT(tags SEPARATOR ';') as `tags.ids`
FROM (
  SELECT t.equipment, GROUP_CONCAT(ttr.tags_id) as `tags`
  FROM task t
    INNER JOIN task_tag_relationship ttr ON ttr.task_id = t.id
  GROUP BY t.equipment, t.id
) t
WHERE equipment = 14
GROUP BY equipment;

Double GROUP_CONCAT with MySQL 5.6

1 Answers