SELECT `a`.*, `b`.`code` as `room_code`, `c`.`name` as `dept_name`, `d`.`name` as `branch_name`, count(distinct e.id) as count_boxes, count(distinct f.id) as count_documents
FROM `kbs__rack` `a`
JOIN `kbs__room` `b` ON `b`.`id` = `a`.`room_id`
LEFT JOIN `company__department` `c` ON `c`.`id` = `a`.`dept_id`
LEFT JOIN `company__branch` `d` ON `d`.`id` = `c`.`branch_id`
LEFT JOIN `kbs__box` `e` ON `e`.`rack_id` = `a`.`id` AND `e`.`is_delete` = 0
LEFT JOIN `kbs__document` `f` ON `f`.`box_id` = `e`.`id` AND `f`.`is_delete` = 0
WHERE `a`.`is_delete` =0
AND `a`.`room_id` = '1'
GROUP BY `a`.`id`