gistfile1.txt

SELECT 
  overlapped_profiles.*
FROM
  (
    SELECT
      profiles.id as profile_id,
      site_id,
      location_id,
  
      profile_type_id
  
    FROM
      associations,
      profiles,
      locations,
      sites
    WHERE
      profiles.site_id = sites.id AND
      locations.id = associations.location_id AND
      associations.profile_id = profiles.id AND
      location_type_id = 5 AND
      profiles.active = true AND
      profiles.entity_id = 6833
  ) AS overlapped_profiles,  
  (
    SELECT
      site_id,
      location_id,
      profile_type_id
    FROM
      associations,
      profiles,
      locations,
      sites
    WHERE
      profiles.site_id = sites.id AND
      locations.id = associations.location_id AND
      associations.profile_id = profiles.id AND
      location_type_id = 5 AND
      profiles.active = true AND
      profiles.entity_id = 6833
    GROUP BY
      site_id,
      location_id,
      profile_type_id
    HAVING
      count(DISTINCT profiles.id) > 1
  ) AS overlapped_locations
WHERE
  overlapped_profiles.profile_type_id = overlapped_locations.profile_type_id AND
  overlapped_profiles.site_id = overlapped_locations.site_id AND
  overlapped_profiles.location_id = overlapped_locations.location_id