CiviCRM Community Forums (archive)

Hi everyone,

I'm wrestling with this report I'm trying to generate about our membership. We have a custom field called "Classification" which supports multi-select values, and I need to report how many members have selected each one of these. I am first trying to build the reports solely with SQL, with an eye to building CiviReports from the queries once they're delivering the right numbers.

When I query this data, though, I'm unable to get sensible totals. For example, if my query is:
SELECT count(*) from civicrm_value_membership__organization__2
WHERE classification_14 LIKE "%Artist%

I get select (*) = 945. So 945 members have selected "Artist" as one of their Classifications. So far so good. When I pull all the possible Classifications from the option list, though, and query for that column using in() or find_in_set(), I get a different, lower total:

SELECT value,  count( * )
FROM civicrm_option_value LEFT JOIN civicrm_value_membership__organization__2
ON value IN(classification_14)
WHERE option_group_id =83
GROUP BY value

In this instance, I get 887 for the total next to "Artist", which is the number of members who have ONLY selected "Artist" and no other option.

I read in http://forum.civicrm.org/index.php/topic,26395.0.html that there is a delimiter (^A) separating all the values in this type of field; alas:

SELECT value,  count( * )
FROM civicrm_option_value LEFT JOIN civicrm_value_membership__organization__2
ON FIND_IN_SET(value, REPLACE(classification_14, '^A',','))
WHERE option_group_id =83
GROUP BY value
returns the same values as the previous query. Swapping IN() for FIND_IN_SET() makes no difference in the query results:

SELECT value,  count( * )
FROM civicrm_option_value LEFT JOIN civicrm_value_membership__organization__2
ON value IN(REPLACE(classification_14, '^A',','))
WHERE option_group_id =83
GROUP BY value
 I figure there are several possibilities:
- the JOIN is trying to match rows on the righthand table to more than one row on the left and is bailing for that reason
- the delimiter is not ^A anymore, or ^A is not how I should be referring to it in PHPMyAdmin where I am testing these queries
- I am stupid for trying to do this in SQL first and I should just go straight to the API

Thoughts?

Running Totten's query in PHPMyAdmin gives me a total of 1 for each possible value:
Artist 1
Artist Management 1
Association 1
Booking Agency 1
Educators 1
Event Management 1
Festivals and Events 1
Lessons - Voice/Instruments 1
Media 1
Producers 1
Publicity and Marketing 1
Record Label 1
Recording Studio 1
Retailer 1
Services 1
Student 1
Technicians 1
Venue 1
Visual Artists and Design 1
Based on other queries I've run against these tables, this says to me that no rows in the civicrm_value_membership__organization__2 are being matched. I'm really starting to think it has something to do with the delimiter; is this a global variable in a settings file somewhere that may be set some other way? Alternately, has anyone had any experiences with PHP MyAdmin not performing SQL queries as expected?

Lobo nailed this one, the issue was indeed that I was literally searching for "^A" and not "CTRL + A". Here's the winning query:
SELECT value,  count( * )
FROM civicrm_option_value
LEFT JOIN civicrm_value_membership__organization__2
  ON classification_14 LIKE concat("%
", value, "
%")
WHERE option_group_id =83
GROUP BY value
The ASCII character doesn't show up here, but came out as a circle in PHP MyAdmin when I pasted it, having copied it from between the quotes in the value for VALUE_SEPARATOR in CRM/Core/DAO.php

I have been banging my head on this for nearly two days; this community RULES.