Answers.Body: <p>You need something like this:</p>
  
  <pre><code>LEFT JOIN civicrm_address address_civireport ON contact_civireport.id = address_civireport.contact_id AND is_primary = 1
  </code></pre>
  
  <p>and then you need the column street_address.
  Enough to get you a step further?</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-03T10:39:47.843Z
Answers.LastActivityDate: 2016-06-03T10:39:47.843Z
Answers.UserDisplayName: ErikH - CiviCooP
Body: <p>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. </p>
  
  <p><a href="https://i.sstatic.net/m4Jwi.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/m4Jwi.png" alt="Report so far"></a></p>
  
  <p>So after the phone column i want to add the address. What code do i need to include?</p>
  
  <p>Here is the code so far:</p>
  
  <p>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  </p>
  
  <p>FROM civicrm_relationship relationship_civireport</p>
  
  <pre><code>         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  ) 
  </code></pre>
  
  <p>LEFT JOIN civicrm_phone phone_a_civireport
                         ON ( contact_civireport.id =
                              phone_a_civireport.contact_id AND
                              phone_a_civireport.is_primary = 1 ) </p>
  
  <p>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)   </p>
  
  <p>GROUP BY relationship_civireport.id    </p>
  
  <p>ORDER BY contact_civireport.sort_name, contact_b_civireport.sort_name   </p>
  
  <p>LIMIT 0, 50</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-06-03T13:47:19.623Z
Comments.Text: Refer to http://civicrm.stackexchange.com/questions/12219/adding-a-column-to-the-standard-relationships-report-in-contact-reports for detailed answer to add new column to an report
Comments.UserDisplayName: Ramesh - ARTECH Consultancy
Tags: <custom-reports>
Title: How to add the address column in a report
ThreadText: # How to add the address column in a report
  
  (Asked by: username_0 on 2016-06-03)
  
  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](https://i.sstatic.net/m4Jwi.png)](https://i.sstatic.net/m4Jwi.png)
  
  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
  
  **Tags:** custom-reports
  
  > **Comments:**
  > **username_1**: Refer to http://civicrm.stackexchange.com/questions/12219/adding-a-column-to-the-standard-relationships-report-in-contact-reports for detailed answer to add new column to an report  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2016-06-03. Score: 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?
  
  
  ---
  
  