mediahome.sql

SELECT drmi.provider_id, dr.provider_profile_selected, dr.provider_profile_respondent,
       dr.provider_profile_media, dr.provider_profile_event, dr.provider_profile_clinical_trial,
       drmr.provider_media_role_name, COUNT(DISTINCT drmi.media_item_id) AS articles,
       dr.provider_name_first, dr.provider_name_last, dr.primary_addr_city, dr.primary_addr_realm,
       dr.primary_addr_zip
FROM media m
     INNER JOIN media_item mi ON (mi.media_id = m.media_id)
     INNER JOIN provider_media_item drmi ON (drmi.media_item_id = mi.media_item_id)
     INNER JOIN provider_media_role drmr ON (drmr.provider_media_role_id = drmi.provider_media_role_id)
     INNER JOIN provider dr ON (dr.provider_id = drmi.provider_id)
     INNER JOIN roi_campaign_project rcp ON (rcp.project_id = dr.project_id)
WHERE rcp.roi_campaign_id = 1 AND m.media_id = 2732
GROUP BY drmi.provider_id, dr.provider_profile_selected, dr.provider_profile_respondent,
         dr.provider_profile_media, dr.provider_profile_event, dr.provider_profile_clinical_trial,
         drmr.provider_media_role_name, dr.provider_name_first, dr.provider_name_last, 
         dr.primary_addr_city, dr.primary_addr_realm,dr.primary_addr_zip