SELECT `a`.*, `b`.*, if(b.is_internal = 1, `d`.`name`, c.name) as customerName
FROM `job__pic` `a`
JOIN `job` `b` ON `b`.`id` = `a`.`job_id`
JOIN `customer` `c` ON `c`.`id` = `b`.`customerid`
LEFT JOIN company__department d ON b.customerID = d.id
WHERE `a`.`staff_id` = '274'
AND `b`.`status` IN(1, 2)
AND `a`.`is_delete` = 0
AND `b`.`is_delete` = 0
AND `b`.`is_pending` = 1
ORDER BY `a`.`created_ts` DESC, `b`.`job_sequence` ASC