Answers.Body: <p>This would be difficult to do in pure SQL in a generic way. I assume this is for some type of reporting system you are connecting to CiviCRM and any scripting is not an option.</p>
  
  <p>For each field where it needs a lookup you'd have to do a join, something like:</p>
  
  <p><code>SELECT c.id,
  cf1.label AS field1_title,
  ov1.label AS field1_label,
  cf2.label AS field2_title,
  ov2.label AS field2_label
  FROM civicrm_contact c
  LEFT JOIN civicrm_value_XXX cv1 ON cv1.entity_id = c.id
  LEFT JOIN civicrm_custom_field cf1 ON cf1.name = 'the field you want #1'
  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 = cv1._the_database_field_name)
  LEFT JOIN civicrm_value_YYY cv2 ON cv2.entity_id = c.id
  LEFT JOIN civicrm_custom_field cf2 ON cf2.name = 'the field you want #2'
  LEFT JOIN civicrm_option_group og2 ON cf2.option_group_id = og2.id
  LEFT JOIN civicrm_option_value ov2 ON (ov2.option_group_id = og2.id AND ov2.value = cv2._the_database_field_name)
  ... more joins for each field...</code></p>
  
  <p><code>XXX depends on the actual table name where the custom field is stored,
  _the_database_field_name is the fieldname as it appears in that table.</code></p>
  
  <p>I use left joins because depending on how contacts get into the system sometimes they are missing stuff, but you'd still want the contact to be output.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-02T15:10:49.220Z
Answers.Text: Understood, and I have this done in SQL similar to this, but the problem I have is when i need to filter the option group in the WHERE it only gives me people who have every custom field, should I be joining on the value and the options table?
  
  "LEFT JOIN civicrm_option_value civicrm_option_value7 ON
   civicrm_option_value7.value = civicrm_value_usa_custom__referee_data_1.license_68   [...]
  WHERE
  civicrm_option_value.option_group_id = 101 "
Answers.UserDisplayName: David Richmond
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-02T15:17:47.263Z
Answers.Text: And yes this is for a reporting solution to connect up with Civi and scripts are not an option.
  
  I need to pull each contact with their associated custom field data but the option_group_id filtering is road blocking me on the where statements
Answers.UserDisplayName: David Richmond
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-02T15:24:45.977Z
Answers.Text: Do you mean something like "only show me contacts that have a non-blank value entered for license"?
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-03T15:18:19.237Z
Answers.Text: Almost I want to see All Contacts and I want to see all non blank values for all custom fields
Answers.UserDisplayName: David Richmond
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-03T16:28:53.623Z
Answers.Text: Do you mean then potentially different numbers of columns for each contact? SQL won't do that. Can you maybe edit your original question with a sketch of the output you'd like?
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-03T18:49:53.787Z
Answers.Text: No same amount of columns for all contacts
Answers.UserDisplayName: David Richmond
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-03T19:48:51.917Z
Answers.Text: Ok, then I don't understand what the filtering/WHERE clause is intended to do. The query like the one I gave will show the value in the column when there is one, and a blank in the column when the value is blank. Where it says "...more joins for each field..." you have to add a similar block of joins for each custom field you have defined. I'll update it to show what it looks like when there's two custom fields defined.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-04T05:38:36.017Z
Answers.Text: so i think the where clause is my problem.
  
  So i have lots of fields with multiple choice options unfortunately the values are 1,2,3,4 ..n
  
  And the labels are, Passed,Failed, etc  so strings.   I can easily make the query i want and get the VALUES but i need the labels instead and since there are tons of values of 1,2,3,4 the only thing that differentiates them is the option group value so that way I know  value 1 is actually label = Passed vs value 1 = some other custom field group.
  
  Does that make senses?
Answers.UserDisplayName: David Richmond
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-04T05:51:13.703Z
Answers.Text: I used the word value incorrectly. I meant label. There is no WHERE clause needed. Let me update the answer again.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-01T20:57:15.610Z
Answers.LastActivityDate: 2018-08-04T05:54:22.770Z
Answers.LastEditDate: 2018-08-04T05:54:22.770Z
Answers.UserDisplayName: Demerit
Body: <p>Any MySQL Civi Gurus out there that know how I can create a query to return all custom value fields with their value per contact id?</p>
  
  <p>I know how to get the occasional custom value, but i need the values not the key field numbers.</p>
  
  <p>My problem is the custom values pick lists are all stored in the same table and so "1" maybe 5 different values and you have to pick the qualifier of each custom custom pick list so that throws off my SQL.</p>
  
  <p>Thank you very much!</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-08-01T06:59:32.267Z
Comments.Text: What you mean with all custom values? Is it the values from all custom data sets? are those sets with multiple records per contact? etc...
Comments.UserDisplayName: Jaap Jansma - CiviCooP
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-08-01T07:46:43.957Z
Comments.Text: I am also unsure if you mean custom fields or option values?
Comments.UserDisplayName: ErikH - CiviCooP
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-08-01T18:10:01.420Z
Comments.Text: for instance i have a custom field called license type, the values are Ref A, Ref B, Ref C,
  
  In the Form
  
  1000,'Referee A'
  
  But i'd like that for All custom fields that exist in the database
Comments.UserDisplayName: David Richmond
Tags: <custom-fields>
Title: MySQL Query to return all custom values
ThreadText: # MySQL Query to return all custom values
  
  (Asked by: username_0 on 2018-08-01)
  
  Any MySQL Civi Gurus out there that know how I can create a query to return all custom value fields with their value per contact id?
  
  I know how to get the occasional custom value, but i need the values not the key field numbers.
  
  My problem is the custom values pick lists are all stored in the same table and so "1" maybe 5 different values and you have to pick the qualifier of each custom custom pick list so that throws off my SQL.
  
  Thank you very much!
  
  **Tags:** custom-fields
  
  > **Comments:**
  > **username_1**: What you mean with all custom values? Is it the values from all custom data sets? are those sets with multiple records per contact? etc...  
  > **username_2**: I am also unsure if you mean custom fields or option values?  
  > **username_0**: for instance i have a custom field called license type, the values are Ref A, Ref B, Ref C,  In the Form  1000,'Referee A'  But i'd like that for All custom fields that exist in the database  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_3 on 2018-08-01. Score: 2
  
  This would be difficult to do in pure SQL in a generic way. I assume this is for some type of reporting system you are connecting to CiviCRM and any scripting is not an option.
  
  For each field where it needs a lookup you'd have to do a join, something like:
  
  `SELECT c.id,
  cf1.label AS field1_title,
  ov1.label AS field1_label,
  cf2.label AS field2_title,
  ov2.label AS field2_label
  FROM civicrm_contact c
  LEFT JOIN civicrm_value_XXX cv1 ON cv1.entity_id = c.id
  LEFT JOIN civicrm_custom_field cf1 ON cf1.name = 'the field you want #1'
  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 = cv1._the_database_field_name)
  LEFT JOIN civicrm_value_YYY cv2 ON cv2.entity_id = c.id
  LEFT JOIN civicrm_custom_field cf2 ON cf2.name = 'the field you want #2'
  LEFT JOIN civicrm_option_group og2 ON cf2.option_group_id = og2.id
  LEFT JOIN civicrm_option_value ov2 ON (ov2.option_group_id = og2.id AND ov2.value = cv2._the_database_field_name)
  ... more joins for each field...`
  
  `XXX depends on the actual table name where the custom field is stored,
  _the_database_field_name is the fieldname as it appears in that table.`
  
  I use left joins because depending on how contacts get into the system sometimes they are missing stuff, but you'd still want the contact to be output.
  
  > **Comments:**
  > **username_0**: Understood, and I have this done in SQL similar to this, but the problem I have is when i need to filter the option group in the WHERE it only gives me people who have every custom field, should I be joining on the value and the options table?  "LEFT JOIN civicrm_option_value civicrm_option_value7 ON  civicrm_option_value7.value = civicrm_value_usa_custom__referee_data_1.license_68   [...] WHERE civicrm_option_value.option_group_id = 101 "  
  > **username_0**: And yes this is for a reporting solution to connect up with Civi and scripts are not an option.  I need to pull each contact with their associated custom field data but the option_group_id filtering is road blocking me on the where statements  
  > **username_3**: Do you mean something like "only show me contacts that have a non-blank value entered for license"?  
  > **username_0**: Almost I want to see All Contacts and I want to see all non blank values for all custom fields  
  > **username_3**: Do you mean then potentially different numbers of columns for each contact? SQL won't do that. Can you maybe edit your original question with a sketch of the output you'd like?  
  > **username_0**: No same amount of columns for all contacts  
  > **username_3**: Ok, then I don't understand what the filtering/WHERE clause is intended to do. The query like the one I gave will show the value in the column when there is one, and a blank in the column when the value is blank. Where it says "...more joins for each field..." you have to add a similar block of joins for each custom field you have defined. I'll update it to show what it looks like when there's two custom fields defined.  
  > **username_0**: so i think the where clause is my problem.  So i have lots of fields with multiple choice options unfortunately the values are 1,2,3,4 ..n  And the labels are, Passed,Failed, etc  so strings.   I can easily make the query i want and get the VALUES but i need the labels instead and since there are tons of values of 1,2,3,4 the only thing that differentiates them is the option group value so that way I know  value 1 is actually label = Passed vs value 1 = some other custom field group.  Does that make senses?  
  > **username_3**: I used the word value incorrectly. I meant label. There is no WHERE clause needed. Let me update the answer again.  
  
  ---
  
  