SELECT `a`.`ID`, `a`.`customerID`, `a`.`departmentID`, `a`.`deadline`, `a`.`description`, `a`.`status`, `a`.`is_pending`, `a`.`is_internal`, `a`.`remarks`, `a`.`yearEndDate`, if(a.is_internal = 1, `d`.`name`, b.name) as customerName, `c`.`name` as `departmentName`, `e`.`name` as `branchName`, group_concat(g.staff_id) as staff_id, group_concat(h.name) as staff_name
FROM `job` `a`
LEFT JOIN customer b ON a.customerID = b.id
LEFT JOIN company__department c ON a.departmentID = c.id
LEFT JOIN company__department d ON a.customerID = d.id
LEFT JOIN company__branch e ON e.id = c.branch_id
LEFT JOIN view__job f ON f.ID = a.ID
LEFT JOIN job__pic g ON g.job_id=a.ID AND g.is_delete = 0
LEFT JOIN user h ON h.id = g.staff_id
WHERE `a`.`is_delete` = 0
AND `g`.`is_delete` = 0
AND `a`.`status` = 1
AND `a`.`deadline` != '0000-00-00'
AND `a`.`departmentID` IN('3')
GROUP BY `a`.`ID`
ORDER BY `a`.`acknowledgeTS` DESC