prob.sql

select provider_identifier_id
from provider_identifier pid
join provider p on p.provider_id = pid.provider_id
join 
(SELECT dr.project_id, drid.identifier_type_id, drid.provider_identifier_identity
     FROM provider_identifier drid
          left outer JOIN provider dr ON (dr.provider_id = drid.provider_id)
                  WHERE identifier_type_id <> 7
     GROUP BY dr.project_id, drid.identifier_type_id, drid.provider_identifier_identity
     HAVING COUNT(*) > 1) as prob on prob.project_id = p.project_id
     and prob.identifier_type_id = pid.identifier_type_id
     and prob.provider_identifier_identity = pid.provider_identifier_identity