CiviCRM Community Forums (archive)

Under the old schema the sql below would return a table with a single entry for each contacts primary email

SELECT 
i.first_name AS 'First Name',
i.last_name AS 'Last Name',
e.email AS 'email'   

FROM    ((civicrm_individual AS i 
LEFT JOIN civicrm_location AS loc ON i.contact_id = loc.entity_id)
LEFT JOIN civicrm_email AS e ON loc.id=e.location_id AND e.is_primary = 1 )

With the schema changed I assumed that I could this

SELECT
c.first_name AS 'First Name',
c.last_name AS 'Last Name',
e.email AS 'email'
   
FROM   
((civicrm_contact AS c
LEFT JOIN civicrm_email AS e ON c.id=e.contact_id AND e.is_primary = 1 )

This is based on this info http://wiki.civicrm.org/confluence/display/CRM/Location+Data

However in my db all the entries in civicrm_email have is_primary = 1.  Surely there should only be a single civicrm_email.is_primary = 1 for each civicrm_email.contact_id??

Is my sql logic wrong or is my email table incorrect?

Looking at my 1.9 tables it looks like the is_primary data is correct. Each civicrm19.email.location_id has only a single is_primary email.

Is it possible that the upgrade process to v2.0 schema has changed this??

I think best approach will be restore you 1.9 db and then go through entire upgrade process.

kurund