SELECT `a`.`code`, `c`.`name` as `dept_name`, `e`.`name` as `dept_branch_name`, `d`.`name` as `location_branch_name`, count(distinct f.id) as count_racks, count(distinct g.id) as count_boxes, count(distinct h.id) as count_documents
FROM `kbs__room` `a`
LEFT JOIN `company__department` `c` ON `c`.`id` = `a`.`dept_id`
LEFT JOIN `company__branch` `d` ON `d`.`id` = `a`.`location_branch_id`
LEFT JOIN `company__branch` `e` ON `e`.`id` = `c`.`branch_id`
LEFT JOIN `kbs__rack` `f` ON `f`.`room_id` = `a`.`id` AND `f`.`is_delete` = 0
LEFT JOIN `kbs__box` `g` ON `g`.`rack_id` = `f`.`id` AND `g`.`is_delete` = 0
LEFT JOIN `kbs__document` `h` ON `h`.`box_id` = `g`.`id` AND `h`.`is_delete` = 0
WHERE `a`.`is_delete` =0
GROUP BY `a`.`id`
ORDER BY `a`.`code` ASC