SELECT `a`.`ID`, `a`.`customerID`, `a`.`departmentID`, `a`.`deadline`, `a`.`description`, `a`.`is_pending`, `b`.`ID` as `picID`, `a`.`is_internal`, `a`.`remarks`, `a`.`yearEndDate`, if(a.is_internal = 1, `d`.`name`, c.name) as customerName, `e`.`name` as `departmentName`, `f`.`name` as `branchName`, `g`.`staff_id`, group_concat(h.name) as staff_name
FROM `job` `a`
LEFT OUTER JOIN job__pic b ON a.ID = b.job_id and b.is_delete =0
LEFT JOIN customer c ON a.customerID = c.id
LEFT JOIN company__department d ON a.customerID = d.id
LEFT JOIN company__department e ON a.departmentID =  e.id
LEFT JOIN company__branch f ON f.id = e.branch_id
LEFT JOIN job__pic g ON g.job_id = a.ID
LEFT JOIN user h ON h.id = g.staff_id
WHERE `b`.`ID` IS NULL
AND `a`.`is_delete` = 0
AND `a`.`departmentID` IN('9')
GROUP BY `a`.`ID`
ORDER BY `a`.`ID` desc