Search code examples
mysqljoinselectinner-joincoalesce

How to correctly use group_concat to concatenate Join query result in MYSQL?

I have written the following MYSQL query and the The Mysql version is 8.0.18-commercial

SELECT p.server, 'Type_ABC' AS Check_Type, COALESCE(vmtable.res) AS result
FROM server p 
INNER JOIN truns t ON t.oq_id = p.oq_id
AND t.id = (SELECT t2.id FROM truns t2 WHERE t2.oq_id = p.oq_id order by t2.created_at desc limit 1 )
INNER JOIN qvuln_info vmtable ON vmtable.run_id = t.id 
LEFT JOIN qvuln_info_data vmtableinfo ON vmtableinfo.qid = vmtable.qid   
WHERE p.server regexp 'server1';

I am getting following output from the above query:

Hostname   Check_Type result
server1    Type_ABC   Result 1
server1    Type_ABC   Result 2
server1    Type_ABC   Result 3
server1    Type_ABC   Result 4

I want to concatenate the results so that the output should look as below:

Hostname   Check_Type  result
server1    Type_ABC    Result 1,,Result 2,,Result 3,,Result 4

To achieve the above output, I have written the following query but it is giving syntax error:

SELECT p.server, 'Type1' AS Check_Type, 
GROUP_CONCAT((COALESCE(vmtable.res) AS result) SEPARATOR ', ')
FROM server p 
INNER JOIN truns t ON t.oq_id = p.oq_id
AND t.id = (SELECT t2.id FROM truns t2 WHERE t2.oq_id = p.oq_id order by t2.created_at desc limit 1 )
INNER JOIN qvuln_info vmtable ON vmtable.run_id = t.id 
LEFT JOIN qvuln_info_data vmtableinfo ON vmtableinfo.qid = vmtable.qid   
WHERE p.server regexp 'server1';

How to correctly use GROUP_CONCAT with COALESCE?

Solution

  • You're missing a GROUP BY clause on your query; you need to GROUP BY p.server. Note that your COALESCE isn't actually doing anything as you haven't provided a value to replace with if the value is NULL, so you may as well leave it out (since GROUP_CONCAT ignores NULL values, this will prevent any values appearing in the result when vmtable.res is NULL). Your query should look like:

    SELECT p.server, 'Type1' AS Check_Type, 
       GROUP_CONCAT(vmtable.res SEPARATOR ', ') AS result
FROM server p 
INNER JOIN truns t ON t.oq_id = p.oq_id
AND t.id = (SELECT t2.id FROM truns t2 WHERE t2.oq_id = p.oq_id order by t2.created_at desc limit 1 )
INNER JOIN qvuln_info vmtable ON vmtable.run_id = t.id 
LEFT JOIN qvuln_info_data vmtableinfo ON vmtableinfo.qid = vmtable.qid   
WHERE p.server regexp 'server1'
GROUP BY p.server

    Also note that the AS result should have been outside the GROUP_CONCAT.