Answers.Body: <p>I'll try to answer specifically for this question but this effectively is a duplicate of <a href="https://civicrm.stackexchange.com/questions/25963/mysql-query-to-return-all-custom-values">MySQL Query to return all custom values</a> because it's the exact same technique.</p>
  
  <p>First, look in civicrm_custom_field in the "name" column for the Background Check Status field. I'm going to assume it says "Background_Check_Status" there. If not you'll need to change it below to match.</p>
  
  <pre><code>SELECT civicrm_contact.id,
  civicrm_contact.display_name,
  civicrm_value_custom__safe_sport_co_9.usoc_sports_safe_complete_9,
  civicrm_value_custom__safe_sport_co_9.background_check_7,
  civicrm_value_custom__safe_sport_co_9.red_light_override_71,
  civicrm_value_custom__safe_sport_co_9.override_expiration_72,
  ov1.label AS 'Background Check Status'
  FROM
  civicrm_contact
  INNER JOIN civicrm_value_custom__safe_sport_co_9 ON civicrm_value_custom__safe_sport_co_9.entity_id = civicrm_contact.id
  LEFT JOIN civicrm_custom_field cf1 ON cf1.name = 'Background_Check_Status'
  LEFT JOIN civicrm_option_group og1 ON cf1.option_group_id = og1.id
  LEFT JOIN civicrm_option_value ov1 ON (ov1.option_group_id = og1.id AND ov1.value = civicrm_value_custom__safe_sport_co_9.background_check_status_73)
  WHERE civicrm_contact.id = 1000
  </code></pre>
  
  <p>Since you already knew the option_group_id, you could use that like so, but I find the name is easier on the eyes.</p>
  
  <pre><code>SELECT civicrm_contact.id,
  civicrm_contact.display_name,
  civicrm_value_custom__safe_sport_co_9.usoc_sports_safe_complete_9,
  civicrm_value_custom__safe_sport_co_9.background_check_7,
  civicrm_value_custom__safe_sport_co_9.red_light_override_71,
  civicrm_value_custom__safe_sport_co_9.override_expiration_72,
  ov1.label AS 'Background Check Status'
  FROM
  civicrm_contact
  INNER JOIN civicrm_value_custom__safe_sport_co_9 ON civicrm_value_custom__safe_sport_co_9.entity_id = civicrm_contact.id
  LEFT JOIN civicrm_option_value ov1 ON (ov1.option_group_id = 110 AND ov1.value = civicrm_value_custom__safe_sport_co_9.background_check_status_73)
  WHERE civicrm_contact.id = 1000
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-14T23:37:07.610Z
Answers.LastActivityDate: 2018-08-14T23:37:07.610Z
Answers.UserDisplayName: Demerit
Answers.Body: <p>You can use <code>public function alterDisplay(&amp;$rows)</code> function of civicrm reports, and add new column, and use civicrm API to populate those columns for custom fields.</p>
  
  <p>Example code:</p>
  
  <pre><code>public function alterDisplay(&amp;$rows) {
  
    foreach ($rows as $rowNum =&gt; $row) {
  
      $this-&gt;_columnHeaders['XYZ_custom_info'] = array(
        'title' =&gt; 'XYZ custom info',
        'type' =&gt; CRM_Utils_Type::T_STRING
      );
  
      try{
        //Getting spouse name
        $field_info = civicrm_api3('Contact', 'get', (
          'return' =&gt; "custom_field_id",
          'id' =&gt; "",
        ));
  
        $rows[$rowNum]['XYZ_custom_info'] = $field_info;
      }
      catch(Exception $e) {
        echo 'Message: ' .$e-&gt;getMessage();
      }
    }
  }
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-06T15:33:56.123Z
Answers.Text: I need to know how to craft the SQL natively I am connecting to Crystal Reports which is another complete system and cannot use the API
Answers.UserDisplayName: David Richmond
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-14T04:44:46.540Z
Answers.Text: Can you help with native SQL?
Answers.UserDisplayName: David Richmond
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-06T15:02:18.150Z
Answers.LastActivityDate: 2018-08-06T15:02:18.150Z
Answers.UserDisplayName: Hassan Farooq
Answers.Body: <p>Another way to do this type of reporting in general is to write a cron script that uses the api and periodically writes out the values to another table that has a schema of your choosing, then have Crystal Reports pull data from that table. The data wouldn't be 100% realtime because there'd be a delay between until the next run of your script, but reporting doesn't always need to be realtime.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-14T23:42:06.197Z
Answers.LastActivityDate: 2018-08-14T23:42:06.197Z
Answers.UserDisplayName: Demerit
Body: <p><a href="https://i.sstatic.net/pP2rT.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/pP2rT.png" alt="Lables of Custom Values"></a>
  This is option group 110 in civicrm_option_value</p>
  
  <p>I am trying to create a custom query for reporting that pulls the actual Custom Data LABELS ('Green Light','Red Light', etc)vs the custom data VALUES (1,2,3 etc) per the CIVI Terminology in Admin.</p>
  
  <p>The following SQL:</p>
  
  <p>SELECT
    civicrm_contact.id,
    civicrm_contact.display_name,
    civicrm_value_custom__safe_sport_co_9.usoc_sports_safe_complete_9,
    civicrm_value_custom__safe_sport_co_9.background_check_7,
    civicrm_value_custom__safe_sport_co_9.red_light_override_71,
    civicrm_value_custom__safe_sport_co_9.override_expiration_72,
    civicrm_value_custom__safe_sport_co_9.background_check_status_73
  FROM
    civicrm_contact
    INNER JOIN civicrm_value_custom__safe_sport_co_9 ON
      civicrm_value_custom__safe_sport_co_9.entity_id =
      civicrm_contact.id
  WHERE
    civicrm_contact.id = 1000</p>
  
  <p>1000,Billy Jenkins,2/27/2018,3/16/2018,0,NULL,1</p>
  
  <p>I actually WANT</p>
  
  <p>1000,Billy Jenkins,2/27/2018,3/16/2018,0,NULL,Green Light</p>
  
  <p>This is micro version of what I actually need which is many more custom data columns but its the same situation, I can't simply join the civicrm_option_value.value field because there are dozens of values of '1' I'm not sure how to segment the 110 group only to pull what I need.</p>
  
  <p>What should I do?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-08-04T15:24:45.187Z
Comments.Text: Possible duplicate of [MySQL Query to return all custom values](https://civicrm.stackexchange.com/questions/25963/mysql-query-to-return-all-custom-values)
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-08-04T19:58:53.047Z
Comments.Text: I would like to delete that question and use this one, i was unable to edit the other one and it seemed like I had too many comments can you help with that?
Comments.UserDisplayName: David Richmond
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-08-05T23:10:19.710Z
Comments.Text: How could i get the results using this exact SQL query, the proposed duplicate question does not answer this
Comments.UserDisplayName: David Richmond
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-08-06T10:12:51.717Z
Comments.Text: Why do you want to do this in SQL?  As you are finding, the relationships between some of the tables are complex.  The recommended way to get data out of Civi is to use the API.  Maybe step back a bit and tell us what the overall aim is and we can guide you to another solution.
Comments.UserDisplayName: Aidan
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-08-06T13:56:50.003Z
Comments.Text: Aim is to produce a SQL query for Crystal reporting to match to another data source, I would like to have all custom fields added to the report in a left join to display their actual labels vs value fields.  If I could get the SQL to generate the label vs the value above i could extrapolate all of the fields myself.
Comments.UserDisplayName: David Richmond
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-08-14T04:44:23.410Z
Comments.Text: No one has a query to solve this problem using the above real sample?
Comments.UserDisplayName: David Richmond
Tags: <custom-fields>
Title: Get Custom CiviCRM Data by ContactID This is not a duplicate
ThreadText: # Get Custom CiviCRM Data by ContactID This is not a duplicate
  
  (Asked by: username_0 on 2018-08-04)
  
  [![Lables of Custom Values](https://i.sstatic.net/pP2rT.png)](https://i.sstatic.net/pP2rT.png)
  This is option group 110 in civicrm\_option\_value
  
  I am trying to create a custom query for reporting that pulls the actual Custom Data LABELS ('Green Light','Red Light', etc)vs the custom data VALUES (1,2,3 etc) per the CIVI Terminology in Admin.
  
  The following SQL:
  
  SELECT
  civicrm\_contact.id,
  civicrm\_contact.display\_name,
  civicrm\_value\_custom\_\_safe\_sport\_co\_9.usoc\_sports\_safe\_complete\_9,
  civicrm\_value\_custom\_\_safe\_sport\_co\_9.background\_check\_7,
  civicrm\_value\_custom\_\_safe\_sport\_co\_9.red\_light\_override\_71,
  civicrm\_value\_custom\_\_safe\_sport\_co\_9.override\_expiration\_72,
  civicrm\_value\_custom\_\_safe\_sport\_co\_9.background\_check\_status\_73
  FROM
  civicrm\_contact
  INNER JOIN civicrm\_value\_custom\_\_safe\_sport\_co\_9 ON
  civicrm\_value\_custom\_\_safe\_sport\_co\_9.entity\_id =
  civicrm\_contact.id
  WHERE
  civicrm\_contact.id = 1000
  
  1000,Billy Jenkins,2/27/2018,3/16/2018,0,NULL,1
  
  I actually WANT
  
  1000,Billy Jenkins,2/27/2018,3/16/2018,0,NULL,Green Light
  
  This is micro version of what I actually need which is many more custom data columns but its the same situation, I can't simply join the civicrm\_option\_value.value field because there are dozens of values of '1' I'm not sure how to segment the 110 group only to pull what I need.
  
  What should I do?
  
  **Tags:** custom-fields
  
  > **Comments:**
  > **username_1**: Possible duplicate of [MySQL Query to return all custom values](https://civicrm.stackexchange.com/questions/25963/mysql-query-to-return-all-custom-values)  
  > **username_0**: I would like to delete that question and use this one, i was unable to edit the other one and it seemed like I had too many comments can you help with that?  
  > **username_0**: How could i get the results using this exact SQL query, the proposed duplicate question does not answer this  
  > **username_2**: Why do you want to do this in SQL?  As you are finding, the relationships between some of the tables are complex.  The recommended way to get data out of Civi is to use the API.  Maybe step back a bit and tell us what the overall aim is and we can guide you to another solution.  
  > **username_0**: Aim is to produce a SQL query for Crystal reporting to match to another data source, I would like to have all custom fields added to the report in a left join to display their actual labels vs value fields.  If I could get the SQL to generate the label vs the value above i could extrapolate all of the fields myself.  
  > **username_0**: No one has a query to solve this problem using the above real sample?  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_1 on 2018-08-14. Score: 1 | Accepted answer
  
  I'll try to answer specifically for this question but this effectively is a duplicate of [MySQL Query to return all custom values](https://civicrm.stackexchange.com/questions/25963/mysql-query-to-return-all-custom-values) because it's the exact same technique.
  
  First, look in civicrm\_custom\_field in the "name" column for the Background Check Status field. I'm going to assume it says "Background\_Check\_Status" there. If not you'll need to change it below to match.
  
  ```
  SELECT civicrm_contact.id,
  civicrm_contact.display_name,
  civicrm_value_custom__safe_sport_co_9.usoc_sports_safe_complete_9,
  civicrm_value_custom__safe_sport_co_9.background_check_7,
  civicrm_value_custom__safe_sport_co_9.red_light_override_71,
  civicrm_value_custom__safe_sport_co_9.override_expiration_72,
  ov1.label AS 'Background Check Status'
  FROM
  civicrm_contact
  INNER JOIN civicrm_value_custom__safe_sport_co_9 ON civicrm_value_custom__safe_sport_co_9.entity_id = civicrm_contact.id
  LEFT JOIN civicrm_custom_field cf1 ON cf1.name = 'Background_Check_Status'
  LEFT JOIN civicrm_option_group og1 ON cf1.option_group_id = og1.id
  LEFT JOIN civicrm_option_value ov1 ON (ov1.option_group_id = og1.id AND ov1.value = civicrm_value_custom__safe_sport_co_9.background_check_status_73)
  WHERE civicrm_contact.id = 1000
  
  ```
  
  Since you already knew the option\_group\_id, you could use that like so, but I find the name is easier on the eyes.
  
  ```
  SELECT civicrm_contact.id,
  civicrm_contact.display_name,
  civicrm_value_custom__safe_sport_co_9.usoc_sports_safe_complete_9,
  civicrm_value_custom__safe_sport_co_9.background_check_7,
  civicrm_value_custom__safe_sport_co_9.red_light_override_71,
  civicrm_value_custom__safe_sport_co_9.override_expiration_72,
  ov1.label AS 'Background Check Status'
  FROM
  civicrm_contact
  INNER JOIN civicrm_value_custom__safe_sport_co_9 ON civicrm_value_custom__safe_sport_co_9.entity_id = civicrm_contact.id
  LEFT JOIN civicrm_option_value ov1 ON (ov1.option_group_id = 110 AND ov1.value = civicrm_value_custom__safe_sport_co_9.background_check_status_73)
  WHERE civicrm_contact.id = 1000
  
  ```
  
  
  ---
  
  ### Answer by username_3 on 2018-08-06. Score: 0
  
  You can use `public function alterDisplay(&$rows)` function of civicrm reports, and add new column, and use civicrm API to populate those columns for custom fields.
  
  Example code:
  
  ```
  public function alterDisplay(&$rows) {
  
    foreach ($rows as $rowNum => $row) {
  
      $this->_columnHeaders['XYZ_custom_info'] = array(
        'title' => 'XYZ custom info',
        'type' => CRM_Utils_Type::T_STRING
      );
  
      try{
        //Getting spouse name
        $field_info = civicrm_api3('Contact', 'get', (
          'return' => "custom_field_id",
          'id' => "",
        ));
  
        $rows[$rowNum]['XYZ_custom_info'] = $field_info;
      }
      catch(Exception $e) {
        echo 'Message: ' .$e->getMessage();
      }
    }
  }
  
  ```
  
  > **Comments:**
  > **username_0**: I need to know how to craft the SQL natively I am connecting to Crystal Reports which is another complete system and cannot use the API  
  > **username_0**: Can you help with native SQL?  
  
  ---
  
  ### Answer by username_1 on 2018-08-14. Score: 0
  
  Another way to do this type of reporting in general is to write a cron script that uses the api and periodically writes out the values to another table that has a schema of your choosing, then have Crystal Reports pull data from that table. The data wouldn't be 100% realtime because there'd be a delay between until the next run of your script, but reporting doesn't always need to be realtime.
  
  
  ---
  
  