CiviCRM Community Forums (archive)

Hi 

I am using Drupal 7.4 and CiviCRM 4.0.4 with Views 3.0-rc1.  I need a distinct list of cities from the Civicrm contacts. So create view select civicrm contacts table and did the following to the view.
 1. Add the Civicrm Address City field
 2. Removed the Contact Id field.
 3. Add Filter Criteria of Location Type and Membership Type and Member Status
All looks good in with the SQL just he civicrm_address.city  not contact_id:
   
SELECT civicrm_address.city AS civicrm_address_city
FROM
{civicrm_contact} civicrm_contact
LEFT JOIN {civicrm_address} civicrm_address ON civicrm_contact.id = civicrm_address.contact_id
LEFT JOIN {civicrm_membership} civicrm_membership ON civicrm_contact.id = civicrm_membership.contact_id
WHERE (( (civicrm_address.location_type_id IN  ('6')) AND (civicrm_membership.membership_type_id IN  ('1', '2')) AND (civicrm_membership.status_id IN  ('1', '2', '3')) ))
ORDER BY civicrm_address_city ASC

4. Now add the Query Settings: Distinct
  It adds the civicrm_contact.id to the sql.  So, now not getting the distinct list of cities because of the field being adding.

SELECT DISTINCT civicrm_address.city AS civicrm_address_city, civicrm_contact.id AS id
FROM
{civicrm_contact} civicrm_contact
LEFT JOIN {civicrm_address} civicrm_address ON civicrm_contact.id = civicrm_address.contact_id
LEFT JOIN {civicrm_membership} civicrm_membership ON civicrm_contact.id = civicrm_membership.contact_id
WHERE (( (civicrm_address.location_type_id IN  ('6')) AND (civicrm_membership.membership_type_id IN  ('1', '2')) AND (civicrm_membership.status_id IN  ('1', '2', '3')) ))
ORDER BY civicrm_address_city ASC

Why is the civicrm_contact.id being added? It is not necessary. I am missing a different way to get a distinct list of all the cities contact member is in?

Any help would be greatly appreciated. Let me know if more information is need.

Thank you,
Pam