 <ol> <li><p>Create a Temporary Table from where condition for acknowledgements, schema will have column required in final result and used in JOIN with all your 7 tables</p> <pre><code>CREATE TEMPORARY TABLE __tempacknowledgements AS SELECT g.name AS hostgroup , '' AS hostname , a.host_id , s.display_name AS servicename , a.service_id , a.entry_time AS ack_time , '' AS AS start_time , '' AS timeperiod , a.state AS state , a.author , a.acknowledgement_id AS ack_id FROM centstorage.acknowledgements a WHERE YEAR(FROM_UNIXTIME( a.entry_time )) = YEAR(CURDATE()) AND MONTH(FROM_UNIXTIME( a.entry_time )) = MONTH(CURDATE()) AND a.service_id IS NOT NULL ORDER BY a.acknowledgement_id ASC; </code></pre></li> </ol> <p>Or create using proper column definition</p> <ol start="2"> <li><p>Update fields from all tables having left join, you can use Inner Join in update. You should write 7 different update statements. 2 examples are given below.</p> <pre><code>UPDATE __tempacknowledgements a JOIN centstorage.hosts h USING(host_id) SET a.name=h.name; UPDATE __tempacknowledgements s JOIN centstorage.services h USING(service_id) SET a.acl_res_name=s.acl_res_name; </code></pre></li> <li><p>similar way update ctime from logs using Join with Logs, this is 8th update statement.</p></li> <li>pick select from temp table. </li> <li>drop temp table</li> </ol> <p>a sp can be written for this.</p>