How many engineer visits are required at most for a single fault log? List the number and the log entry id.
select count ( * ), t1.fault_log_entry_id from fault_log as t1 join engineer_visits as t2 on t1.fault_log_entry_id = t2.fault_log_entry_id group by t1.fault_log_entry_id order by count ( * ) desc limit 1
What is the engineer visit id of that fault log entry id?
select t2.engineer_visit_id from fault_log as t1 join engineer_visits as t2 on t1.fault_log_entry_id = t2.fault_log_entry_id where t2.fault_log_entry_id = ( select t1.fault_log_entry_id from fault_log as t1 join engineer_visits as t2 on t1.fault_log_entry_id = t2.fault_log_entry_id group by t1.fault_log_entry_id order by count ( * ) desc limit 1 )
What is the contact staff id for that?
select t2.contact_staff_id from fault_log as t1 join engineer_visits as t2 on t1.fault_log_entry_id = t2.fault_log_entry_id where t2.fault_log_entry_id = ( select t1.fault_log_entry_id from fault_log as t1 join engineer_visits as t2 on t1.fault_log_entry_id = t2.fault_log_entry_id group by t1.fault_log_entry_id order by count ( * ) desc limit 1 )
What is the fault status of that?
select t2.fault_status from fault_log as t1 join engineer_visits as t2 on t1.fault_log_entry_id = t2.fault_log_entry_id where t2.fault_log_entry_id = ( select t1.fault_log_entry_id from fault_log as t1 join engineer_visits as t2 on t1.fault_log_entry_id = t2.fault_log_entry_id group by t1.fault_log_entry_id order by count ( * ) desc limit 1 )
What's the fault description for that?
select t1.fault_description from fault_log as t1 join engineer_visits as t2 on t1.fault_log_entry_id = t2.fault_log_entry_id group by t1.fault_log_entry_id order by count ( * ) desc limit 1
Tell me what the other fault details for that are, please?
select t1.other_fault_details from fault_log as t1 join engineer_visits as t2 on t1.fault_log_entry_id = t2.fault_log_entry_id group by t1.fault_log_entry_id order by count ( * ) desc limit 1