2

Im trying to add address columns to the report. Either a supplemental 1 or 2 address column. Ive got all the other criteria sorted but am strugling with the address.

Report so far

So after the phone column i want to add the address. What code do i need to include?

Here is the code so far:

SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name_a, contact_civireport.id as civicrm_contact_id, contact_b_civireport.sort_name as civicrm_contact_b_sort_name_b, contact_b_civireport.id as civicrm_contact_b_id, phone_a_civireport.phone as civicrm_phone_phone_a

FROM civicrm_relationship relationship_civireport

         INNER JOIN civicrm_contact contact_civireport
                    ON ( relationship_civireport.contact_id_a =
                         contact_civireport.id )

         INNER JOIN civicrm_contact contact_b_civireport
                    ON ( relationship_civireport.contact_id_b =
                         contact_b_civireport.id )


    INNER JOIN civicrm_relationship_type relationship_type_civireport
                    ON ( relationship_civireport.relationship_type_id  =
                         relationship_type_civireport.id  ) 

LEFT JOIN civicrm_phone phone_a_civireport ON ( contact_civireport.id = phone_a_civireport.contact_id AND phone_a_civireport.is_primary = 1 )

WHERE ( ( contact_civireport.contact_sub_type IN ( 'Residential_Park') ) ) AND ( ( contact_b_civireport.contact_type IN ( 'Household') ) ) AND (contact_civireport.is_deleted = 0 AND contact_b_civireport.is_deleted = 0)

GROUP BY relationship_civireport.id

ORDER BY contact_civireport.sort_name, contact_b_civireport.sort_name

LIMIT 0, 50

1

You need something like this:

LEFT JOIN civicrm_address address_civireport ON contact_civireport.id = address_civireport.contact_id AND is_primary = 1

and then you need the column street_address. Enough to get you a step further?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.