How to add the address column in a report

Question

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.

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

Answer 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?