gistfile1.txt

select dr.provider_id, dr.provider_name_first, dr.provider_name_last, dr.primary_addr_line_1, dr.primary_addr_city, 
       dr.primary_addr_city, dr.primary_addr_realm, dr.primary_addr_zip, s.specialty_name,
       ims.provider_identifier_identity as ims, me.provider_identifier_identity as me, npi.provider_identifier_identity as npi
from provider dr
     left outer join provider_specialty drs on (drs.provider_id = dr.provider_id and drs.provider_specialty_primary = 1)
     left outer join specialty s on (s.specialty_id = drs.specialty_id)
     left outer join provider_identifier npi on (npi.provider_id = dr.provider_id and npi.identifier_type_id = 22)
     left outer join provider_identifier me on (me.provider_id = dr.provider_id and me.identifier_type_id = 2)
     left outer join provider_identifier ims on (ims.provider_id = dr.provider_id and ims.identifier_type_id = 3)
where project_id = 41 
      and dr.provider_id in (select distinct provider_id from event_provider where event_role_id = 1)