CiviCRM Community Forums (archive)

Hey everyone,

so I'm getting this database error when I try to do an advanced search, with a custom search view, for individuals in a certain state.   I can see where the SQL goes wrong, but I don't have the ability to jump in the code to fix it just yet, nor can I find the time given my schedule...    anyway, I can see that the SQL never declares civicrm_email `1-email` like it does with the other fields.  im not sure why it does this or how to fix it....  any ideas?     DB Error follows:



    Sorry. A non-recoverable error has occurred.

    DB Error: no such field

    Database Error Code: Unknown column '1-email.location_type_id' in 'on clause', 1054

    Return to home page.

Error Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

=> -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a
LEFT JOIN civicrm_address `1-address` ON (`1-address`.contact_id = contact_a.id AND `1-address`.is_primary = 1)  LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )
LEFT JOIN civicrm_state_province `1-state_province` ON `1-state_province`.id = `1-address`.state_province_id  LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id 
LEFT JOIN civicrm_location_type `1-location_type` ON (  ( `1-address`.location_type_id = `1-location_type`.id ) OR ( `1-email`.location_type_id = `1-location_type`.id )OR ( `1-phone-Phone`.location_type_id = `1-location_type`.id ) )  WHERE  ( contact_a.contact_type IN ('Individual') AND LOWER(civicrm_state_province.name) = 'massachusetts' )  AND  ( 1 )     [nativecode=1054 ** Unknown column '1-email.location_type_id' in 'on clause']
    [type] => DB_Error
    [user_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a
LEFT JOIN civicrm_address `1-address` ON (`1-address`.contact_id = contact_a.id AND `1-address`.is_primary = 1)  LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )
LEFT JOIN civicrm_state_province `1-state_province` ON `1-state_province`.id = `1-address`.state_province_id  LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id 
LEFT JOIN civicrm_location_type `1-location_type` ON (  ( `1-address`.location_type_id = `1-location_type`.id ) OR ( `1-email`.location_type_id = `1-location_type`.id )OR ( `1-phone-Phone`.location_type_id = `1-location_type`.id ) )  WHERE  ( contact_a.contact_type IN ('Individual') AND LOWER(civicrm_state_province.name) = 'massachusetts' )  AND  ( 1 )     [nativecode=1054 ** Unknown column '1-email.location_type_id' in 'on clause']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a
LEFT JOIN civicrm_address `1-address` ON (`1-address`.contact_id = contact_a.id AND `1-address`.is_primary = 1)  LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )
LEFT JOIN civicrm_state_province `1-state_province` ON `1-state_province`.id = `1-address`.state_province_id  LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id 
LEFT JOIN civicrm_location_type `1-location_type` ON (  ( `1-address`.location_type_id = `1-location_type`.id ) OR ( `1-email`.location_type_id = `1-location_type`.id )OR ( `1-phone-Phone`.location_type_id = `1-location_type`.id ) )  WHERE  ( contact_a.contact_type IN ('Individual') AND LOWER(civicrm_state_province.name) = 'massachusetts' )  AND  ( 1 )     [nativecode=1054 ** Unknown column '1-email.location_type_id' in 'on clause']"]
)