mysql group_concat on multiple tables

Question

This is the first time I ever use stackoverflow,.. be gentle on me ;)

I have little problem here with getting duplicated results from GROUP_CONCAT when using more than one JOIN on the map tables.

It is not easy to explain this, but I will try:

I have created a SQLFiddle for testing: http://sqlfiddle.com/#!9/d2b347/3

I want the query to be just one instead of 1 for all of the posts and then hammering on every test. But since the GROUP_CONCAT is merging those test results I am getting twice as much data than I want.

It is possible somehow to make the query more reliable. To always have the GROUP_CONCAT to be the exact numbers of tests?

I expect/want the output to be:

|---------|-----------------|------------|---------|-------------|
| post_id | flows           | flow_types | powers  | power_types |
|---------|-----------------|------------|---------|-------------|
|       1 | 100,140         | a,b        | 1,1     | a,b         |
|---------|-----------------|------------|---------|-------------|
|       2 | 200,200,200     | a,b,c      | (null)  | (null)      |
|---------|-----------------|------------|---------|-------------|

but it is:

|---------|-----------------|------------|---------|-------------|
| post_id | flows           | flow_types | powers  | power_types |
|---------|-----------------|------------|---------|-------------|
|       1 | 100,100,140,140 | a,a,b,b    | 1,1,1,1 | a,b,a,b     |
|---------|-----------------|------------|---------|-------------|
|       2 | 200,200,200     | a,b,c      | (null)  | (null)      |
|---------|-----------------|------------|---------|-------------|

and with GROUP_CONCAT DISTINCT I get:

|---------|-----------------|------------|---------|-------------|
| post_id | flows           | flow_types | powers  | power_types |
|---------|-----------------|------------|---------|-------------|
|       1 | 100,140         | a,b        | 1       | a,b         |
|---------|-----------------|------------|---------|-------------|
|       2 | 200             | a,b,c      | (null)  | (null)      |
|---------|-----------------|------------|---------|-------------|

Here is the create query:

DROP TABLE IF EXISTS `posts`;
CREATE TABLE IF NOT EXISTS `posts` (
  `post_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post` varchar(256) CHARACTER SET ascii NOT NULL,
  PRIMARY KEY (`post_id`),
  UNIQUE KEY `UNQ_post` (`post`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `posts_test1`;
CREATE TABLE IF NOT EXISTS `posts_test1` (
  `post_id` bigint(20) unsigned NOT NULL,
  `test1_id` bigint(20) unsigned NOT NULL,
  `type_id` int(10) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`post_id`,`test1_id`,`type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test1`;
CREATE TABLE IF NOT EXISTS `test1` (
  `test1_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `flow` int(10) unsigned NOT NULL,
  PRIMARY KEY (`test1_id`),
  KEY `IDX_FLOW` (`flow`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `posts_test2`;
CREATE TABLE IF NOT EXISTS `posts_test2` (
  `post_id` bigint(20) unsigned NOT NULL,
  `test2_id` bigint(20) unsigned NOT NULL,
  `type_id` int(10) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`post_id`,`test2_id`,`type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test2`;
CREATE TABLE IF NOT EXISTS `test2` (
  `test2_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `power` int(10) unsigned NOT NULL,
  PRIMARY KEY (`test2_id`),
  KEY `IDX_POWER` (`power`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `types`;
CREATE TABLE IF NOT EXISTS `types` (
  `type_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(50) CHARACTER SET ascii DEFAULT NULL,
  PRIMARY KEY (`type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `types` (`type_id`, `type`) VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c');

INSERT INTO `posts` (`post_id`, `post`) VALUES
  (1, 'test1'),
  (2, 'test2');

INSERT INTO `test1` (`test1_id`, `flow`) VALUES
  (1, 100),
  (2, 140),
  (3, 200),
  (4, 200),
  (5, 200);

INSERT INTO `posts_test1` (`post_id`, `test1_id`, `type_id`) VALUES
  (1, 1, 1),
  (1, 2, 2),
  (2, 3, 1),
  (2, 4, 2),
  (2, 5, 3);

INSERT INTO `test2` (`test2_id`, `power`) VALUES
  (1, 1),
  (2, 1);

INSERT INTO `posts_test2` (`post_id`, `test2_id`, `type_id`) VALUES
  (1, 1, 1),
  (1, 2, 2);

And here are my select queries..

SELECT
p.post_id, p.post,
GROUP_CONCAT(t1.flow) flow,
GROUP_CONCAT(t1t.type) flow_types
FROM posts p
LEFT JOIN posts_test1 pt1 USING (post_id)
    LEFT JOIN test1 t1 USING (test1_id)
        LEFT JOIN types t1t ON (t1t.type_id = pt1.type_id)
GROUP BY p.post_id; # works fine


SELECT
p.post_id, p.post,
GROUP_CONCAT(t2.power) powers,
GROUP_CONCAT(t2t.type) power_types
FROM posts p
LEFT JOIN posts_test2 pt2 USING (post_id)
    LEFT JOIN test2 t2 USING (test2_id)
        LEFT JOIN types t2t ON (t2t.type_id = pt2.type_id)
GROUP BY p.post_id; # works fine


SELECT
p.post_id, p.post,
GROUP_CONCAT(t1.flow) flow,
GROUP_CONCAT(t1t.type) flow_types,
GROUP_CONCAT(t2.power) powers,
GROUP_CONCAT(t2t.type) power_types
FROM posts p
LEFT JOIN posts_test1 pt1 USING (post_id)
    LEFT JOIN test1 t1 USING (test1_id)
        LEFT JOIN types t1t ON (t1t.type_id = pt1.type_id)
LEFT JOIN posts_test2 pt2 USING (post_id)
    LEFT JOIN test2 t2 USING (test2_id)
        LEFT JOIN types t2t ON (t2t.type_id = pt2.type_id)
GROUP BY p.post_id; # getting duplicated GROUP_CONCAT results

SELECT
p.post_id, p.post,
GROUP_CONCAT(DISTINCT t1.flow) flow,
GROUP_CONCAT(DISTINCT t1t.type) flow_types,
GROUP_CONCAT(DISTINCT t2.power) powers,
GROUP_CONCAT(DISTINCT t2t.type) power_types
FROM posts p
LEFT JOIN posts_test1 pt1 USING (post_id)
    LEFT JOIN test1 t1 USING (test1_id)
        LEFT JOIN types t1t ON (t1t.type_id = pt1.type_id)
LEFT JOIN posts_test2 pt2 USING (post_id)
    LEFT JOIN test2 t2 USING (test2_id)
        LEFT JOIN types t2t ON (t2t.type_id = pt2.type_id)
GROUP BY p.post_id; # DISTINCT wipes the GROUP_CONCAT if same result...

Thanks and have a nice day!!

edit: added expected result as suggest, thanks :)

Please add expected output in tabular formatting to the question. By the way, well done on the question formulation :) — Madhur Bhaiya, Nov 26 '18 at 11:54

score 1 · Accepted Answer · answered Nov 26 '18 at 13:59

The issue here is that there are two different junction tables (and two different connection chains), originating from a single table post. So a linear JOIN chain does not work. Duplicates in one of the junction table leads to duplication in other chain, when linear joining is done.

One way is to consider these two different JOIN chains in two separate Derived Tables (subqueries inside the FROM clause), and determine their respective grouped/aggregated expressions. We can then JOIN back these two chains using post_id.

Query

SELECT
  dt1.post_id, 
  dt1.flows, 
  dt1.flow_types, 
  dt2.powers, 
  dt2.power_types 
FROM 
(
  SELECT 
    p.post_id, 
    GROUP_CONCAT(t1.flow) AS flows, 
    GROUP_CONCAT(typ.type) AS flow_types
  FROM posts p
  LEFT JOIN posts_test1 pt1 
    ON pt1.post_id = p.post_id 
  LEFT JOIN test1 t1 
    ON t1.test1_id = pt1.test1_id 
  LEFT JOIN types typ 
    ON typ.type_id = pt1.type_id 
  GROUP BY p.post_id 
) AS dt1 
JOIN 
(
  SELECT 
    p.post_id, 
    GROUP_CONCAT(t2.power) AS powers, 
    GROUP_CONCAT(typ.type) AS power_types 
  FROM posts p
  LEFT JOIN posts_test2 pt2 
    ON pt2.post_id = p.post_id 
  LEFT JOIN test2 t2 
    ON t2.test2_id = pt2.test2_id 
  LEFT JOIN types typ 
    ON typ.type_id = pt2.type_id 
  GROUP BY p.post_id 
) AS dt2
  ON dt1.post_id = dt2.post_id;

Result

| post_id | flows       | flow_types | powers | power_types |
| ------- | ----------- | ---------- | ------ | ----------- |
| 1       | 100,140     | a,b        | 1,1    | a,b         |
| 2       | 200,200,200 | a,b,c      |        |             |

View on DB Fiddle

Thanks Madhur :) I was hoping to avoid using subquery since I need to do way more than just 2 "mapped joins" — TLBang, Nov 26 '18 at 15:04
@TLBang I don't believe that it is solvable without using subqueries; due to different join chains. Other possible solution would involve correlated subqueries. — Madhur Bhaiya, Nov 26 '18 at 16:20
@TLBang writing a large query should not be an issue as long it is performant (with proper indexing defined) and does the job. — Madhur Bhaiya, Nov 27 '18 at 04:09

mysql group_concat on multiple tables

1 Answers1