6

I am trying to write a query using group_concat, concat and other functions, but I am getting stuck not able to join and group the data to represent them in 1 cell.

Here's how my data is...

table1
+-----+---------+
| id  | ...     |
+-----+---------+
| 1   |         |
| 2   |         |
| 3   |         |
+-----+---------+
table2
+-----+-----+-------+
| id  | env | infid |
+-----+-----+-------+
| 1   | p   | 10    |
| 1   | p   | 11    |
| 1   | p   | 20    |
| 1   | p   | 12    |
| 1   | D   | 21    |
| 1   | D   | 22    |
+-----+-----+-------+
table3
+-------+---------+---------+
| infid | inftype | infname |
+-------+---------+---------+
| 10    | Srv     | abc     |
| 20    | Srv     | xyz     |
| 11    | Db      | hgj     |
| 12    | Db      | kjk     |
| 21    | Srv     | pop     |
| 22    | Db      | kli     |
+-------+---------+---------+

expected query result

+-----+------------------------------+---------------------+
| id  | P                            | D                   |
+------------------------------------+---------------------+
| 1   |Srv: [abc, xyz] Db: [hgj, kjk]|Srv: [pop] Db: [Kli] |
+-----+------------------------------+---------------------+

Any help is greatly appreciated.

  • You should do this transformation in application code, not in SQL – jkavalik Aug 11 '15 at 19:05
  • Is it not possible to get the result through SQL. I may be able to get them in different columns, but how could i join/combine them ? – Kumar Aug 11 '15 at 19:08
  • You can do it in SQL, probably stored procedure/function will be much easier than SELECT with concats and group_concats - but it is not the right place to do it - sql is for data storage and retrieval, formatting the output is for app code which is usually much better suited to handle it. – jkavalik Aug 11 '15 at 19:14
2

The first thing you need is a messy query to create each column

FIRST PHASE OF QUERY

SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env;

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS kumar_concat;
Query OK, 3 rows affected (0.03 sec)

mysql> CREATE DATABASE kumar_concat;
Query OK, 1 row affected (0.00 sec)

mysql> USE kumar_concat
Database changed
mysql> CREATE TABLE table1
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO table1 VALUES (),(),();
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table2
    -> (
    ->     id INT NOT NULL,
    ->     env VARCHAR(10) NOT NULL,
    ->     infid INT NOT NULL,
    ->     PRIMARY KEY (id,infid)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO table2 (id,env,infid) VALUES
    -> (1,'P',10), (1,'P',11), (1,'P',20),
    -> (1,'P',12), (1,'D',21), (1,'D',22);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table3
    -> (
    ->     infid INT NOT NULL,
    ->     inftype VARCHAR(10) NOT NULL,
    ->     infname VARCHAR(10) NOT NULL,
    ->     PRIMARY KEY (infid)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO table3 (infid,inftype,infname) VALUES
    -> (10,'Srv','abc'), (20,'Srv','xyz'), (11,'Db','hgj'),
    -> (12,'Db','kjk'), (21,'Srv','pop'), (22,'Db','kli');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

FIRST PHASE OF QUERY EXECUTED

mysql> SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY env,inftype) A GROUP BY env;
+-----+------------------------------+
| env | tags                         |
+-----+------------------------------+
| D   | Srv [pop] Db [kli]           |
| P   | Srv [abc, xyz] Db [hgj, kjk] |
+-----+------------------------------+
2 rows in set (0.02 sec)

mysql>

Let's create each for each env value

QUERY

SELECT
    T1.tags P,T2.tags D
FROM
(SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env) T1,
(SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env) T2
WHERE T1.env='P'
AND T2.env='D';

QUERY EXECUTED

mysql> SELECT
    ->     T1.tags P,T2.tags D
    -> FROM
    -> (SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY env,inftype) A GROUP BY env) T1,
    -> (SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY env,inftype) A GROUP BY env) T2
    -> WHERE T1.env='P'
    -> AND T2.env='D';
+------------------------------+--------------------+
| P                            | D                  |
+------------------------------+--------------------+
| Srv [abc, xyz] Db [hgj, kjk] | Srv [pop] Db [kli] |
+------------------------------+--------------------+
1 row in set (0.05 sec)

mysql>

FINAL QUERY (with id wedged in)

SELECT
    T1.id,T1.tags P,T2.tags D
FROM
(SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY id,env,inftype) A GROUP BY id,env) T1
INNER JOIN
(SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY id,env,inftype) A GROUP BY id,env) T2 
USING (id) WHERE T1.env='P' AND T2.env='D';

FINAL QUERY (with id wedged in) EXECUTED

mysql> SELECT
    ->     T1.id,T1.tags P,T2.tags D
    -> FROM
    -> (SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY id,env,inftype) A GROUP BY id,env) T1
    -> INNER JOIN
    -> (SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY id,env,inftype) A GROUP BY id,env) T2
    -> USING (id) WHERE T1.env='P' AND T2.env='D';
+----+------------------------------+--------------------+
| id | P                            | D                  |
+----+------------------------------+--------------------+
|  1 | Srv [abc, xyz] Db [hgj, kjk] | Srv [pop] Db [kli] |
+----+------------------------------+--------------------+
1 row in set (0.08 sec)

mysql>

GIVE IT A TRY !!!

  • 4
    MY BRAIN HURTS !!! – RolandoMySQLDBA Aug 11 '15 at 19:39
  • This query get messier if you want to accommodate servers that are only in one environment. – RolandoMySQLDBA Aug 11 '15 at 20:00
  • 1
    What can i say, Rolando you are the BEST. When you say "My BRAIN HURTS", i can only say, MY BRAIN IS DEAD, your query helped me recover :) – Kumar Aug 11 '15 at 20:15
  • I have 3 more values in Table 2 env column and when i modified the query, i get an ambiguous error for id field. – Kumar Aug 11 '15 at 20:38
7

It is possible to get to the desired result in a simpler way.

First, you could start with this simple query:

SELECT
  t1.id,
  t2.env,
  CONCAT(t3.inftype,
         ': [',
         GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '),
         ']'
        ) AS inftypeandnames
FROM
  table1 AS t1
  INNER JOIN table2 AS t2 ON t1.id = t2.id
  INNER JOIN table3 AS t3 ON t2.infid = t3.infid
GROUP BY
  t1.id,
  t2.env,
  t3.inftype

This is what you would get as the result for your data samples:

id  env  inftypeandnames
--  ---  ---------------
1   D    Db: [kli]
1   D    Srv: [pop]
1   P    Db: [hgj, kjk]
1   P    Srv: [abc, xyz]

Next, you could use the above result as a derived table and group it by id, env, concatenating the inftypeandnames values, like this:

SELECT
  id,
  env,
  GROUP_CONCAT(inftypeandnames ORDER BY inftypeandnames DESC SEPARATOR ' ') AS inftypesandnames
FROM
  (
    SELECT
      t1.id,
      t2.env,
      CONCAT(t3.inftype,
             ': [',
             GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '),
             ']'
            ) AS inftypeandnames
    FROM
      table1 AS t1
      INNER JOIN table2 AS t2 ON t1.id = t2.id
      INNER JOIN table3 AS t3 ON t2.infid = t3.infid
    GROUP BY
      t1.id,
      t2.env,
      t3.inftype
  ) AS s
GROUP BY
  id,
  env

The result would be very close to what you want:

id  env  inftypesandnames
--  ---  ------------------------------
1   D    Srv: [pop] Db: [kli]
1   P    Srv: [abc, xyz] Db: [hgj, kjk]

In order to split the inftypesandnames values into separate columns based on the values of env, you could duplicate the above query, apply a filter on env to each copy (env = 'D', env = 'P' and so on if there can be others), join the results and return each subset's inftypesandnames value in its own column – same as Rolando has suggested, although I would argue that you might need a full outer join rather than an inner join if it is possible for some ids to have only D while for some others only P. Since MySQL does not support the FULL JOIN / FULL OUTER JOIN syntax and the usual workaround to that is a union of a left join and a right join, I suspect the final query in this case would make Rolando's brain hurt even more.

However, there is no need to resort to duplicating subqueries like that, because you can pivot the results using conditional aggregation. Instead of grouping by id, env in the outer query, you would be grouping by id only and conditionally apply GROUP_CONCAT to the inftypeandnames values based on env, like this:

SELECT
  id,
  GROUP_CONCAT(CASE env WHEN 'P' THEN inftypeandnames END ORDER BY inftypeandnames DESC SEPARATOR ' ') AS P,
  GROUP_CONCAT(CASE env WHEN 'D' THEN inftypeandnames END ORDER BY inftypeandnames DESC SEPARATOR ' ') AS D
FROM
  (
    SELECT
      t1.id,
      t2.env,
      CONCAT(t3.inftype,
             ': [',
             GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '),
             ']'
            ) AS inftypeandnames
    FROM
      table1 AS t1
      INNER JOIN table2 AS t2 ON t1.id = t2.id
      INNER JOIN table3 AS t3 ON t2.infid = t3.infid
    GROUP BY
      t1.id,
      t2.env,
      t3.inftype
  ) AS s
GROUP BY
  id
;

You can verify for yourself that the result of the last query would match your requirements:

id  P                               D
--  ------------------------------  --------------------
1   Srv: [abc, xyz] Db: [hgj, kjk]  Srv: [pop] Db: [kli]
  • Awesome, just what i needed. Thanks Andriy and for the explanation too. It really helps understand how best to structure the SQL syntax. I also observed that the query takes much less time to execute when compared to he complex joins based model that Rolando suggested, any guidance on how your query is optimized. – Kumar Aug 12 '15 at 3:24
  • The most likely reason Rolando's code doesn't perform very well is that it requests the two halves of the results separately, so it ends up hitting each of the underlying tables twice. It is also possible (not sure how likely, though, can't verify) that the T1.env='P' AND T2.env='D' conditions applied at the outermost level are not pushed down to the underlying instances of table2 – so if there is an index that includes the env column, it might not be used efficiently. (You'd need to look at the query plan to see if that is true.) In contrast, my query splits the results in one go. – Andriy M Aug 12 '15 at 7:24
0

You could use those examples with GROUP_CONCAT:

Query 1:

SELECT
    t1.id,
    t2.env,
    CASE WHEN t2.env='P' THEN GROUP_CONCAT(CONCAT(t3.inftype,': [',t3.infname,'] ') SEPARATOR ' ') 
     WHEN t2.env='D' THEN GROUP_CONCAT(CONCAT(t3.inftype,': [',t3.infname,'] ') SEPARATOR ' ') END AS 'infname'
FROM test.table1 as t1
JOIN test.table2 as t2 ON (t2.id=t1.id)
JOIN test.table3 as t3 ON (t2.infid=t3.infid)
GROUP BY t1.id,t2.env;

Result Q1:

+----+-----+-----------------------------------------------+
| id | env | infname                                           |
+----+-----+-----------------------------------------------+
|  1 | D   | Db: [kli]  Srv: [pop]                         |
|  1 | P   | Srv: [xyz]  Db: [kjk]  Db: [hgj]  Srv: [abc]  |
+----+-----+-----------------------------------------------+
2 rows in set (0.00 sec)

Query 2:

SELECT
    t1.id,
    t2.env,
    GROUP_CONCAT(CONCAT(t3.inftype,': [',t3.infname,'] ') SEPARATOR ' ') AS infname
FROM test.table1 as t1
JOIN test.table2 as t2 ON (t2.id=t1.id)
JOIN test.table3 as t3 ON (t2.infid=t3.infid)
GROUP BY t1.id,t2.env;

Result Q2:

+----+-----+-----------------------------------------------+
| id | env | infname                                       |
+----+-----+-----------------------------------------------+
|  1 | D   | Db: [kli]  Srv: [pop]                         |
|  1 | P   | Db: [hgj]  Db: [kjk]  Srv: [abc]  Srv: [xyz]  |
+----+-----+-----------------------------------------------+
2 rows in set (0.00 sec)
  • Thanks oNare, I was trying to get them to a single row, but your solution is also good. – Kumar Aug 11 '15 at 20:24
  • Don't worry, I didn't try my best. :P – oNare Aug 11 '15 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.