Código:
SELECT P.provider_id, P.company_name, P.country, P.medical_speciality, C.status2 from provider P inner join provider_call_log_id C on P.Provider_id=C.Provider_Id where
C.provider_call_log_id IN (SELECT Max(provider_call_log_id) from provider_call_log where provider_id=".$_GET['provider_id'].")
AND P.active='prospect'
Creo que es esto lo que buscas. Un saludo.