How can I join these rows together with commas as a column in my select?

I have the following query:

SELECT 
    STRING_AGG(SELECT department FROM additionaldepartments WHERE employee = e.EMPID, ',') AS additional_departments,
    r.actionId,
    e.EMPID AS `id`,
    CONCAT(e.FIRSTNAME, ' ', e.LASTNAME) AS `name`,
    b.ID AS `branch_id`,
    b.NAME AS `branch_name`,
    b.EMAIL AS `branch_email`,
    d.departmentDescription AS `primary_department`,
    j.JobType AS `job_role`
FROM
    actionplanresponsibleemployees r
        JOIN
    employee e ON e.EMPID = r.employeeId
        JOIN
    branch b ON b.ID = e.BRANCHID
        LEFT JOIN
    hremploymentdepartments d ON d.departmentPK = e.DepartmentFK
        JOIN
    hrjobtype j ON j.JobTypeID = e.JOBTYPE

The first line of the select will not work, I know this, but I’m just trying to demonstrate what it is I want from this query,

What I’m trying to achieve, is to have another column in my select called additional_departments which would be the department field from my additionaldepartments table, joined together in a string separated with commas.

Answer

What you want is GROUP_CONCAT():

SELECT 
    (SELECT GROUP_CONCAT(department) 
     FROM additionaldepartments 
     WHERE employee = e.EMPID) AS additional_departments,
    r.actionId,
    ........................

Advertisement

Answer